How do I split these in multiple columns A1 = red;orange;yellow;indigo;blue;dog
I will have the first column as dump and formula on the succeeding columns to split them by semi colon.
...
Best Answer

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Hey man, client is a bit demanding.
 Is there a way to eliminate the “;” in columns BE? 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.
...
Help Article Resources
Categories
Check out the Formula Handbook template!