Split Text to Columns Using Formula?
If I have a column with text like this:
Mac:Air
Mac:Pro
Lenovo:Yoga
Using formulas, how can I pull the data that comes before and after the semicolons? I want to end up with 1 column like this:
Mac
Mac
Lenovo
And another column like this:
Air
Pro
Yoga
Thank you.
Comments

Hi,
This is how I achieved the results you are requesting:
 Add 2 columns, with the following formulas per column
 =LEFT([Column6]2, FIND(":", [Column6]2)  1)
 =RIGHT([Column6]2, LEN([Column6]2)  FIND(":", [Column6]2))
You'll need to change the column names and row# (or use @row) to suit your circumstances.
See the screenshot.
I hope this helps?
Sean

Yes! Thank you!
At the same time, I also found a way through a nested IF with some FINDS and SUBSTITUTES. But your way is simpler.

This is one of those things that Excel just does better. Instead of going through the whole formula route, I put my information into Excel first. I can quickly and easily separate the text into two columns, then upload to Smartsheet.

Hi,
If I need to split the information between 3 different column how can I do?
Now I have:
SN 002121982/ KS 9324/ Line E4
How can I split it in 3 different columns?
Thank you!

@Adina Mirea Check out this post. This might get you pointed in the right direction.
And also this post https://community.smartsheet.com/discussion/86237/extractmultiplepiecesofatextstringthatmeetacriteriabetweenparentheses
Help Article Resources
Categories
Check out the Formula Handbook template!