How do I split these in multiple columns A1 = red;orange;yellow;indigo;blue;dog

heyjay

I will have the first column as dump and formula on the succeeding columns to split them by semi colon.

Andrée Starå

Hi @heyjay

I hope you're well and safe!

Try something like this.

I've added a closing ; to your text, so it looks like this

red;orange;yellow;indigo;blue;dog;

I've also named the example columns as follows.

The one with the text is named Text, and the others are named A, B, C >

• Add the following formula to column A

=LEFT(Text@row, FIND(";", Text@row) - 1)

• Add the following formula to column B and drag the formula across the rest of the columns on the same row.

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:A@row, ";") + ";", ""), 
FIND(";", SUBSTITUTE($Text@row, JOIN($A@row:A@row, ";") + ";", "")) - 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @heyjay

I hope you're well and safe!

Try something like this.

I've added a closing ; to your text, so it looks like this

red;orange;yellow;indigo;blue;dog;

I've also named the example columns as follows.

The one with the text is named Text, and the others are named A, B, C >

• Add the following formula to column A

=LEFT(Text@row, FIND(";", Text@row) - 1)

• Add the following formula to column B and drag the formula across the rest of the columns on the same row.

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:A@row, ";") + ";", ""), 
FIND(";", SUBSTITUTE($Text@row, JOIN($A@row:A@row, ";") + ";", "")) - 1)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

heyjay

Thanks Andree, we were also able to solve by using plus sign instead of Join. And the formula is very similar to what you have provided.

Andrée Starå

@heyjay

Excellent!

You're more than welcome!

heyjay

Hey man, client is a bit demanding.

• Is there a way to eliminate the “;” in columns B-E? So instead of the first example reading “abc;”, could it read “abc”? - Solved this by modifying your formula

=LEFT(SUBSTITUTE($[Group Data]@row, JOIN($[Data 1]@row:[Data 1]@row, ";") + ";", ""), FIND(";", SUBSTITUTE($[Group Data]@row, JOIN($[Data 1]@row:[Data 1]@row, ";") + ";", "")) - 1)

• Also, is there a way for it to still pull the last one if it doesn’t end in “;”?  - Since our formula is range, im stuck unfortunately.