Results 1 to 6 of 6

Thread: Get Field from Delimited Text String

  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.

  2. #2
    Junior Member
    Join Date
    Mar 2014
    Posts
    1
    Rep Power
    0

    Why the -1 to get 98

    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!



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


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 12-14-2023 at 02:45 AM.

  3. #3
    Junior Member
    Join Date
    Feb 2015
    Posts
    3
    Rep Power
    0
    Hi Rick. Re your comment
    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
    Last edited by DocAElstein; 07-07-2023 at 01:07 PM.

  4. #4
    Junior Member
    Join Date
    Feb 2015
    Posts
    3
    Rep Power
    0
    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.

  5. #5
    Junior Member
    Join Date
    Dec 2016
    Posts
    1
    Rep Power
    0
    Last edited by DocAElstein; 12-14-2023 at 02:39 AM. Reason: Took out the complete Post Qoute

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by VictorSum View Post
    I registered on this site just to say THANK YOU for this solution. I've been looking for this for days!
    Thank you for registering and taking the time to offer your thanks for my posting... I am glad you found it useful in some way.

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
  •