Rick Rothstein
03-15-2012, 12:51 AM
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/get-reversed-field-delimited-text-string-334/#post1157
=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/get-reversed-field-delimited-text-string-334/#post1157