Help

Re: Split Text to Multiple Columns

Solved
Jump to Solution
2526 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Ryan_de_Metz
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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.
1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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}, "(?:,)([^,]*)$")))

Screen Shot 2021-11-12 at 7.27.38 AM

See Solution in Thread

2 Replies 2
Justin_Barrett
18 - Pluto
18 - Pluto

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}, "(?:,)([^,]*)$")))

Screen Shot 2021-11-12 at 7.27.38 AM

Ellie_Cissel
5 - Automation Enthusiast
5 - Automation Enthusiast

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?