Split Text to Columns Using Formula?

SYSPK · 05/28/19

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

Lenovo

And another column like this:

Air

Pro

Yoga

Thank you.

sean59916 · 05/28/19

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 · 05/29/19

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 · 10/21/20

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 · 04/12/21

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 · 07/07/22

@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

Split Text to Columns Using Formula?

Comments

Help Article Resources

Categories