Pull Text from String using Delimiter

Ramsay Zaki

I would like to parse the text from a string in another cell. However, the string might not always be the same length so I cannot use LEFT or RIGHT.

Here are two examples of what the string might look like:

1,5,7,33,15

13,22,8,1,16

Both examples above have 5 numbers separated by commas. What would the formula be to always get the 3rd number (7 and 8 in the examples above)?

Paul Newcome

First we start by finding the first delimiter:

=FIND(",", [String Column]@row)

Then we can use this plus 1 to tell another FIND function where to start searching the string so that we can locate the second delimiter:

=FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1)

Adding one to that will give us the first character to pull:

=FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1

Next we need to find the third delimiter, so we use the above as the position to start in for another FIND function:

=FIND(",", [String Column]@row, FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1)

We subtract the number generated by (second delimiter + 1) from the third delimiter, and we will know how many characters to pull:

=FIND(",", [String Column]@row, FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1) - (FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1)

Now that we have our starting point and our number of characters, we can drop that into a MID statement:

=MID([String Column]@row, starting point, number of characters)

=MID([String Column]@row, FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1, number of characters)

=MID([String Column]@row, FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1, FIND(",", [String Column]@row, FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1) - (FIND(",", [String Column]@row, FIND(",", [String Column]@row) + 1) + 1))

Luis Salguero

This formula was essential to something I'm working on.

@Paul Newcome is it possible for you to share a formula for pulling the text for the 4th and 5th numbers from this example?

Thank you.

Paul Newcome

@Luis Salguero I'm not sure I understand what you need. Are you able to provide a screenshot that shows what you are working with and then manually entered data to show the desired outcome?

glitchwizard

is there no better way to do this than nested loops?

Kelly Moore

Hey @glitchwizard

You may want to see the answer from @Leibel Shuchat . It might get you what you need. I've been able to apply it to many parsing scenarios

https://community.smartsheet.com/discussion/86237/extract-multiple-pieces-of-a-text-string-that-meet-a-criteria-between-parentheses

Kelly

Caitlin Jirovsky

@Paul Newcome You have no idea how many times I have referenced this answer! You are a GENIUS! Thank you!

cabbsman

How do I pull data from the 4th delimiter instead of the 3rd?

Paul Newcome

@cabbsman Exactly what solution are you currently using?

cabbsman

cabbsman

@Paul Newcome Here's the formula that pulls the 3rd string using the delimited pipes from A | ANA | Cisco | D| E | F| G|H:

=MID(Summary@row, FIND("|", Summary@row, FIND("|", Summary@row) + 1) + 1, FIND("|", Summary@row, FIND("|", Summary@row, FIND("|", Summary@row) + 1) + 1) - (FIND("|", Summary@row, FIND("|", Summary@row) + 1) + 1))

I modified the formula found in this article where I changed the column to "Summary" and the commas in the FIND to pipes, ie, "|". And as the article solution describes, it successfully pulls the 3rd string from the delimited pipes.

I need to modify the formula above so the 7th string in one case is pulled and in another case the 6th string is pulled.

Paul Newcome

@cabbsman This nested FIND solution is honestly obsolete. Take a look through the thread linked earlier in this one by Kelly. It will outline how to leverage the SUBSTITUTE function which is much easier to adapt for different delimiter sets.