Skip to main content
Solved

Split Text to Multiple Columns


Hey everyone,


I’ve been spinning my wheels on a simple issue for the last few days and hoping someone can put me out of my misery.



I have the above dataset and hoping to split it up automatically using a formula. The data comes in via Zapier and the delimiter is always a ,. I need a solution that can run automatically.


Few keynotes on the data;



  1. Could be anything from 1-3 records. They won’t always be the same

  2. The length of them will also vary


FWIW I have only two records now and have formulas in place based on searching around. They are;


Product ID A


IF( FIND(",", {Product ID purchased}),

  LEFT({Product ID purchased},

    FIND(",", {Product ID purchased})-1

  ),

  {Product ID purchased}

) ```




Product ID B


  RIGHT({Product ID purchased},

    LEN({Product ID purchased}) - LEN({Product ID A}) - 1

  ),

  FIND( ",", 

        RIGHT({Product ID purchased},

          LEN({Product ID purchased}) - LEN({Product ID A}) - 1

        ) & ","

      )-1

) ```



[quote="Ryan_de_Metz, post:1, topic:44313, full:true"]

Hey everyone, 



I've been spinning my wheels on a simple issue for the last few days and hoping someone can put me out of my misery.



![CleanShot 2021-11-12 at 13.43.54|700x102](upload://2q12QRyYR4LdKirNZSh5t9Dhnzd.png)



I have the above dataset and hoping to split it up automatically using a formula. The data comes in via Zapier and the delimiter is always a `,`. I need a solution that can run automatically.  



Few keynotes on the data;



1. Could be anything from 1-3 records. They won't always be the same

2. The length of them will also vary



FWIW I have only two records now and have formulas in place based on searching around. They are;



`Product ID A`




IF( FIND(",", {Product ID purchased}),

LEFT({Product ID purchased},

FIND(",", {Product ID purchased})-1

),

{Product ID purchased}

) ```


`Product ID B`



```LEFT(

  RIGHT({Product ID purchased},

    LEN({Product ID purchased}) - LEN({Product ID A}) - 1

  ),

  FIND( ",", 

        RIGHT({Product ID purchased},

          LEN({Product ID purchased}) - LEN({Product ID A}) - 1

        ) & ","

      )-1

) ```



I've looked at the various other threads on the forum and tried a few different options and just can't get to seem to get the records out when 3 records are present.



Appreciate any advice or guidance you can offer to me.

Best answer by Justin_Barrett

I was about to reply in the other thread where you’d asked this question, but I see you’ve started a new thread for it (not recommended when you’ve already asked a question elsewhere), so I’ll just reply here.


First off, there’s an issue with how you’re trying to insert your formulas into your post. The graves triplets—these things: ```—need to be on their own line, with no other text on the same line. That’s why your formatting above isn’t working.


That aside, here’s how to do this using regular expressions:


Product ID A:


IF({Product ID purchased}, REGEX_EXTRACT({Product ID purchased}, "[^,]*"))


Product ID B:


IF(FIND(",", {Product ID purchased}), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)"))


Product ID C:


IF(FIND(",", {Product ID purchased}, LEN({Product ID A}) + 2), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)$"))


The formulas for extracting the costs are similar, but we can look for the presence of an extracted ID to determine whether or not to extract the related cost.


Product Cost A:


IF({Product ID A}, VALUE(REGEX_EXTRACT({Product Costs}, "[^,]*")))


Product Cost B:


IF({Product ID B}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)")))


Product Cost C:


IF({Product ID C}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)$")))


View original
Did this topic help you find an answer to your question?

3 replies

  • Inspiring
  • 4647 replies
  • Answer
  • November 12, 2021

I was about to reply in the other thread where you’d asked this question, but I see you’ve started a new thread for it (not recommended when you’ve already asked a question elsewhere), so I’ll just reply here.


First off, there’s an issue with how you’re trying to insert your formulas into your post. The graves triplets—these things: ```—need to be on their own line, with no other text on the same line. That’s why your formatting above isn’t working.


That aside, here’s how to do this using regular expressions:


Product ID A:


IF({Product ID purchased}, REGEX_EXTRACT({Product ID purchased}, "[^,]*"))


Product ID B:


IF(FIND(",", {Product ID purchased}), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)"))


Product ID C:


IF(FIND(",", {Product ID purchased}, LEN({Product ID A}) + 2), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)$"))


The formulas for extracting the costs are similar, but we can look for the presence of an extracted ID to determine whether or not to extract the related cost.


Product Cost A:


IF({Product ID A}, VALUE(REGEX_EXTRACT({Product Costs}, "[^,]*")))


Product Cost B:


IF({Product ID B}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)")))


Product Cost C:


IF({Product ID C}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)$")))



  • New Participant
  • 3 replies
  • February 14, 2023
Justin_Barrett wrote:

I was about to reply in the other thread where you’d asked this question, but I see you’ve started a new thread for it (not recommended when you’ve already asked a question elsewhere), so I’ll just reply here.


First off, there’s an issue with how you’re trying to insert your formulas into your post. The graves triplets—these things: ```—need to be on their own line, with no other text on the same line. That’s why your formatting above isn’t working.


That aside, here’s how to do this using regular expressions:


Product ID A:


IF({Product ID purchased}, REGEX_EXTRACT({Product ID purchased}, "[^,]*"))


Product ID B:


IF(FIND(",", {Product ID purchased}), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)"))


Product ID C:


IF(FIND(",", {Product ID purchased}, LEN({Product ID A}) + 2), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)$"))


The formulas for extracting the costs are similar, but we can look for the presence of an extracted ID to determine whether or not to extract the related cost.


Product Cost A:


IF({Product ID A}, VALUE(REGEX_EXTRACT({Product Costs}, "[^,]*")))


Product Cost B:


IF({Product ID B}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)")))


Product Cost C:


IF({Product ID C}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)$")))



Hi! These formulas work amazing for what I need - but needing a two more fields in addition: "Product ID D" and "Product ID E". Could you help me with these formulas?


  • New Participant
  • 3 replies
  • February 21, 2025
Justin_Barrett wrote:

I was about to reply in the other thread where you’d asked this question, but I see you’ve started a new thread for it (not recommended when you’ve already asked a question elsewhere), so I’ll just reply here.


First off, there’s an issue with how you’re trying to insert your formulas into your post. The graves triplets—these things: ```—need to be on their own line, with no other text on the same line. That’s why your formatting above isn’t working.


That aside, here’s how to do this using regular expressions:


Product ID A:


IF({Product ID purchased}, REGEX_EXTRACT({Product ID purchased}, "[^,]*"))


Product ID B:


IF(FIND(",", {Product ID purchased}), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)"))


Product ID C:


IF(FIND(",", {Product ID purchased}, LEN({Product ID A}) + 2), REGEX_EXTRACT({Product ID purchased}, "(?:,)([^,]*)$"))


The formulas for extracting the costs are similar, but we can look for the presence of an extracted ID to determine whether or not to extract the related cost.


Product Cost A:


IF({Product ID A}, VALUE(REGEX_EXTRACT({Product Costs}, "[^,]*")))


Product Cost B:


IF({Product ID B}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)")))


Product Cost C:


IF({Product ID C}, VALUE(REGEX_EXTRACT({Product Costs}, "(?:,)([^,]*)$")))



I must learn more about REGEX now - thank you!


Reply