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

Lenovo

And another column like this:

Air

Pro

Yoga

Thank you.

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Need Formula for Subtracting Order Quantities from Inventory Quantities
Hi! What formula would I use in Smartsheet to automatically subtract a quantity from one sheet based on what is submitted in another sheet? I have one sheet that is where we will manage the "orders submitted" and on that sheet is different columns for different items that can be ordered and the quantities requested for…
Employee Reviews - Pulling & Averaging Category Scores
Hi all, thanks in advance for your assistance! I am working on building a new employee review process for my company and am caught up on one of the final steps. Here's where I am at: I have created an employee review form that individual managers will complete Each employee will have 3-5 reviewers The form submissions will…
Pulling in data from 1 sheet to another isn't working with any function.
PMO Template - I want to pull in the Project Name Column from the Project Metadata sheet into my custom Attendance Sheet > Project Name Column. I've tried VLOOKUP, INDEX MATCH, JOIN COLLECT, I've changed parenthesis to brackets. I am new to Smartsheets and can't get anything to work. I've watch videos, followed community…