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