Split Text to Columns Using Formula?

SYSPK

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.

sean59916

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

SYSPK

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.

carriej2

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.

Adina Mirea

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!

Anelise Wilhelm

@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/extract-multiple-pieces-of-a-text-string-that-meet-a-criteria-between-parentheses