Results 1 to 6 of 6

Thread: Get Field from Delimited Text String

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    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
    Last edited by Rick Rothstein; 05-07-2014 at 07:02 PM.

Similar Threads

  1. Get "Reversed" Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 3
    Last Post: 02-22-2015, 09:01 AM
  2. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  3. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  4. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  5. Find the First or Last So Many Words in a Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 06-21-2012, 09:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •