Results 1 to 4 of 4

Thread: Get "Reversed" Field from Delimited Text String

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    Get "Reversed" Field from Delimited Text String

    Yesterday, I posted a formula to get a specified field of text from within a delimited text string (see "http://www.excelfox.com/forum/f22/ge...xt-string-333/" for the descriptions that will be assumed in this article). It occurred to me that sometimes it is necessary to retrieve the field in reverse; that is, you might want, say, the second field from the end of the text without knowing in advance how many total fields there are. This formula will do that...

    See FOLLOW UP NOTE below for a better formula...
    =TRIM(MID(SUBSTITUTE(A1,delimiter,REPT(" ",99)),(2-fieldnumber+(LEN(A1)-LEN(SUBSTITUTE(A1,delimiter,"")))/LEN(delimiter))*99-98,99))

    Note the delimiter (which can still be one or more characters long) appears three times in the formula, so you have to make sure you replace that keyword with the actual delimiter in all three locations. As an example, lets say you have this text in cell A1...

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

    The delimiter here is " - " (a space/dash/space). Let's say you wanted the third field from the end (the "eight" in this example). Substituting " - " for all three "delimiter" keywords and 3 for the "fieldnumber" keyword yields this...

    =TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",99)),(2-3+(LEN(A1)-LEN(SUBSTITUTE(A1," - ","")))/LEN(" - "))*99-98,99))

    You might want to simplify the 2-3 part and replace it with -1, but I probably would leave the rest of the formula as is.

    FOLLOW UP NOTE
    -------------------------
    Haseeb Avarakkan responded with a much better formula (it involves three less function calls meaning it should be more efficient) than the one I posted above. Here is his formula in a generalized format...

    =TRIM(LEFT(RIGHT(SUBSTITUTE(delimiter&A1,delimiter ,REPT(" ",99)),fieldnumber*99),99))

    Note that there are only two delimiter replacements that need to be made (instead of the three that my formula required). Thank you for posting that Haseeb, much appreciated! 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.

    Here is my example from above using Haseeb's formula instead...

    =TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",99)),3*99),99))

    which can be simplified to this by performing the math operation (3*99)...

    =TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",99)),297),99))
    Last edited by Rick Rothstein; 05-07-2014 at 07:04 PM. Reason: Reduced the 999 values to 99 to allow code to handle longer text

  2. #2
    Junior Member Haseeb A's Avatar
    Join Date
    Apr 2011
    Posts
    21
    Rep Power
    0
    Rick, nice tip

    =TRIM(MID(SUBSTITUTE(A1," - ",REPT(" ",999)),(2-3+(LEN(A1)-LEN(SUBSTITUTE(A1," - ","")))/LEN(" - "))*999-998,999))
    I think this gives error, If fieldnumber > delimiter eg: 11 in the sample

    I think LEFT/RIGHT may be shorter

    =TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",999)),3*999),999))

    Also If the fieldnumber > Total_delimiter shows as blank.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Haseeb Avarakkan View Post
    I think LEFT/RIGHT may be shorter

    =TRIM(LEFT(RIGHT(SUBSTITUTE(" - "&A1," - ",REPT(" ",999)),3*999),999))
    Excellent! Thank you so much for posting your message. I have added a note to the original thread message citing your formula.

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

    It turned out that this alternative version that takes the length of the string into consideration is slightly more efficient over 1000 rows of sample data:
    Code:
    =TRIM(LEFT(RIGHT(SUBSTITUTE(delimiter&text,delimiter,REPT(" ",LEN(text))),fieldnumber*LEN(text)),LEN(text)))

    So my advices would be to use this 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.
    Last edited by JeffreyWeir; 02-23-2015 at 03:01 AM.

Similar Threads

  1. VBA Versions of my "Get Field" and "Get Reverse Field" formulas
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 06-02-2017, 06:15 PM
  2. Get Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-05-2017, 01:24 AM
  3. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  4. Find a text substring that matches a given "pattern"
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 02-10-2013, 06:19 AM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

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