Pull Text from String using Delimiter
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)?
Answers
-
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))
-
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.
-
@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?
-
is there no better way to do this than nested loops?
-
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
Kelly
-
@Paul Newcome You have no idea how many times I have referenced this answer! You are a GENIUS! Thank you!
-
How do I pull data from the 4th delimiter instead of the 3rd?
-
@cabbsman Exactly what solution are you currently using?
-
-
@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.
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!