Get Field from Delimited Text String

How many times have you had a delimited text string (say "one,two,three,four" where the delimiter is a comma) and you wanted a formula to retrieve one of the delimited values, say, the third one. Personally, I have wondered for years why such a function is not built into Excel but, alas, it isn't. We could use VB to create a UDF (user defined function), but as it turns out, that is not necessary. Here is a relatively simple formula that will parse delimited text out from within a larger text string up to 99 characters long...

=TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),fieldnumber*99-98,99))

You simply have to replace the word "delimiter" with a text value (one or more characters long) and replace the word "fieldnumber" with the number (starting at 1) for the field of text you want. For example, let's say the cell A1 has this text in it...

one,two,three,four,five,six,seven,eight,nine,ten

This formula can be used to retrieve, say, the seventh field of data...

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),7*99-98,99))

or, simplifying the expression by performing the indicated math toward the end of the formula...

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),595,99))

Remember, the delimiter does not have to be a single character. Let's say your this text was in cell A1...

one - two - three - four - five - six - seven

and you wanted, say, the third field using the dash as the delimiter. You could use "-" as the delimiter and then apply the TRIM function to it in order to get rid of the leading/trailing spaces that would attach to the text returned if only as single dash was used for the delimiter; however, if we use " - " (space/dash/space) as the delimiter, then the desired result would be returned directly...

=TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",99)),3*99-98,99))

or, again, simplifying by completing the indicated math...

=TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",99)),199,99))

Recently, in another forum, someone asked for a formula to split text delimited by and underscore into adjacent columns. So, basically, that person had text structured like this in Column A...

one_two_three_four_five_six_seven

and wanted it split out so that "one" was in Column B, "two" was in Column C, and so on. This is how I responded using the above formula as a basis...

Assuming your data starts in Row 1, put this simple formula in B1 and copy it across for as many columns as you think you will ever have need for, then select B1 over to that column and copy those cells down as far as needed...

=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",99)),COLUMN(A1)*99-98,99))

The key to this version of the formula working is the COLUMN(A1)... in the first cell, it evaluates to 1 so the formula returns the first field. When dragged across, the A1 becomes B1 so the second formula has COLUMN(B1) which evaluates to 2 for the field number, and so on for the third, fourth, etc. formulas in the remaining dragged across cells. When copied down, the column references don't change, the row references do, but since the row number is immaterial to the column number, the formulas all continue to work as they should in the cells the formulas are dragged down to. The only caution is that the text in A1 must be less than 100 characters (that is what the 99 establishes). If your text is longer, then you would need to change the three 99's in the formula to a number equal to, or larger than, the maximum number of characters the text in A1 can have (change the 98 to one less than that number).

Hopefully, when the situation presents itself, you will find ways in which to put this formula to use in your own Excel worksheets.

See this article for an extension to the above formula: http://www.excelfox.com/forum/f22/ge...-334/#post1157

This is a very handy formula: =TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),7*99-98,99)). It is much shorter and has fewer functions than some of the ones that I have used. I have been trying to understand it and the part that evades my understanding is the:

7*99-98

I see that the multiplying of "number of the field you want to extract" * "the max number of characters" (7*99) gets you into the "sea of spaces" past the "field you want to extract" (seventh field). And I understand that the subtraction gets you back to the "sea of spaces" before the "field you want to extract", but why does it need the minus 1 (98 = 99-1)? What is the situation that would lead to trouble if you did not have the minus 1?

Thanks in advance!

Hi Rick. Re your comment

Quote:

---

The only caution is that the text in A1 must be less than 100 characters (that is what the 99 establishes). If your text is longer, then you would need to change the three 99's in the formula to a number equal to, or larger than, the maximum number of characters the text in A1 can have (change the 98 to one less than that number).

---

...I guess one could always set that multiplier dynamically e.g.:

Code:

---

=TRIM(MID(SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))),element*LEN(Text)-(LEN(Text)-1),LEN(Text)))

---

There's a few more function calls involved, so I don't know what it means in terms of efficiency.

Here's a formula I use, by the way:

Code:

---

=MID(Text,FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element)),SUM(FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element+{0,1}))*{-1,1})-LEN(Delim))

---

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

It turned out that the generalized alternative version that takes the length of the string into consideration i.e. this one

Code:

---

=TRIM(MID(SUBSTITUTE(Text,Delim,REPT(" ",LEN(Text))),element*LEN(Text)-(LEN(Text)-1),LEN(Text)))

---

...was slightly faster on 1000 rows of sample data as the original formula.

And my alternative formula:

Code:

---

=MID(Text,FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element)),SUM(FIND("|",SUBSTITUTE(Delim&Text&Delim,Delim,"|",element+{0,1}))*{-1,1})-LEN(Delim))

---

...was slightly slower.

So my advices would be to use the generalized version, that not only is guaranteed to work with your data under greater string lengths, but is also likely to save Excel some effort. Not that you'll notice it: we are talking hundredths of a second over 1000 rows of sample data.

Fortunately, it's so much easier now:
=INDEX(TEXTSPLIT(A1,"delimiter"),fieldnum)

Scott - Yes, it, along with a whole host of new dynamic array functionality, is so much easier now. Of course, I posted my original mini-article some 13+ years ago, well before this all was even a twinkle in Bill Gates' eye.