Split Text to Multiple Columns

Ryan_de_Metz · ‎Nov 12, 2021

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;

Could be anything from 1-3 records. They won’t always be the same
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.

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

See Solution in Thread

Justin_Barrett · ‎Nov 12, 2021