How to Do a Reverse String Search in Excel Using FIND

Customizing the Reverse String Search Formula

The full reverse string search formula is as follows:

=IF(ISERROR(FIND("X",A1)),A1,RIGHT(A1,LEN(A1)-
FIND("~",SUBSTITUTE(A1,"X","~",LEN(A1)-LEN(SUBSTITUTE(A1,"X",""))))))

To customize the formula to your uses, replace the red X‘s with the search value. Make sure the green tilde (~) does not appear anywhere in your string, or change it to a character that definitely won’t appear if you are unsure. Finally, change the references to cell A1 in blue to the cell that contains the string you want to search inside.

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

28 thoughts on “How to Do a Reverse String Search in Excel Using FIND

  1. Improvement using mid instead of right?!?

    A1=The quick brown fox jumps over the lazy dog.

    =IF(ISERROR(FIND(” “,A1)),A1,MID(A1,FIND(“~”,SUBSTITUTE(A1,” “,”~”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))))+1,LEN(A1)))

  2. Please check your fourmula – when converting it to Danish Excel i find I have to make the following change (A1);A!) etc. and not A1));A1 etc.:

    =HVIS(HVIS.FEJL(FIND(” “;A1);A1);MIDT(A1;(FIND(“~”;UDSKIFT(A1;” “;”~”;LÆNGDE(UDSKIFT(A1;” “;”~”))-LÆNGDE(UDSKIFT(A1;” “;””)))))+1;LÆNGDE(A1)))

    1. The formula uses ISERROR, not IFERROR, I’m not fluent in Danish but I think you should use ER.FEJL and not HVIS.FEJL.

    1. Beautiful!

      Good tips stand the test of time, thanks!

      FYI, my mid string reverse search was not quite as simple, but modified from Deepak’s version, works for most of the text strings I need. Data set coding is inconsistent, so I’m always left with some manual cleanup.

      =VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(

      (TRIM(LEFT(SUBSTITUTE(TRIM($A1),”XYZ”,REPT(” “,99)),99)))

      ), ” “, REPT(” “, 99)), 99)))

      Essentially, find string “XYZ”, and THEN find the string BEFORE that (again delimited with ” “, but still could be any other delimiter or another substring).

      In my case, I’m looking for product wgts buried anywhere within a product description.

      “bucket o’ widgets, 21.5 oz, yellow polka dotted” (search of ” oz”, look backwards for 21.5, converted to VALUE). similar for lb, kg, or cnt, etc.

    1. That really is a bit of impressive latteral thinking to create a reverse find – I was looking for a vba solution to create a customer right find function, but that really is impressive…. sometimes you just get schooled… thanks Andrew

  3. Awsome use of functions to do someting that is not inherent in excel. Used the concept to find initials for a cell with FM&L name or a F&L name.

  4. Thank you for the excellent sample and explanation.
    Was able to easily change to fit my requirements.
    Thank you Andrew!!!

  5. This seems like just what I need, except it’s not working if I have more than one character as my search value. I’m a beginner with this stuff, and any simple tips would be appreciated.

  6. =MID(A11,FIND(“~”,SUBSTITUTE(A11,” “,”~”,LEN(A11)-LEN(SUBSTITUTE(A11,” “,””))),1)+1,999)

    for extracting text from right before space

  7. A) Tweaking the structure of IF(ISERROR( to IFERROR and parsing as a value:

    =IFERROR(VALUE(RIGHT(A1,
    LEN(A1)-FIND(“~”,
    SUBSTITUTE(A1,” “,”~”,
    LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))
    )
    )
    )),A1)

    B) Using a User Defined Function with InStrRev:

    – Functions reside in Personal.XLSB,but can be viewed if not protected in VB Editor
    – Functions (in Personal.XLSB) load at startup from XLStart and are global
    – Path: C:\Users\[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

    Public Function MyV(CellRef As String)

    ‘Creative Commons License – Fred Kagel

    ‘Reverse string search looking for normal space

    Dim i As Integer
    CellRef = Trim(CellRef)
    i = InStrRev(CellRef, ” “) ‘finds position of space from end of string
    MyV = Val(Right(CellRef, Len(CellRef) – i)) ‘Parse as value

    End Function

    1. To clarify the value of i above: inStrRev finds the first space from the end of the string (going right to left), but i returns the position of the space counting from the beginning of the string (left to right). To see the value of i, enter the MsgBox line:

      Public Function MyV(CellRef As String)
      Dim i As Integer
      CellRef = Trim(CellRef)
      i = InStrRev(CellRef, ” “) ‘finds position of space from end
      Msgbox (“The value of i is: ” & i) ‘but counts left to right — thanks to Henry H
      MyV = Val(Right(CellRef, Len(CellRef) – i))

      End Function

      Also, functions do not have to be global via Personal.xlsb; they may be self-contained within a module of a given workbook.

    1. No need to use VBA… use this formula to get the same thing… position of first space from right of a string in cell A1…

      =IF(ISERROR(FIND(” “,A1,1)),0,FIND(“\”,SUBSTITUTE(A1,” “,”\”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))),1))
      returns 0 (zero) if no spaces in the string…

      To find first space to left of any position in a string, use…

      =IF(ISERROR(FIND(” “,LEFT(A1,n),1)),0,FIND(“\”,SUBSTITUTE(A1,” “,”\”,LEN(LEFT(A1,n))-LEN(SUBSTITUTE(LEFT(A1,n),” “,””))),1))
      where n=desired position, returns 0 (zero) if no spaces left of position n

  8. Possible to do a reverse lookup for multiple character types? So what if I wanted to do the reverse lookup until either a “.” or a line break (CHAR(10))?

  9. Hello,
    I would like to extract some text from a cell that has this string:

    Street name, Nr. 2, Bl. X5, Sc. B, Ap. 15

    (sometimes string differes in format)
    Street Name, Nr. 3, Bl. X2, Sc. C, Et. 3, Ap. 12

    I cant use fixed length because not all cells are the same format, so i need to search for string, find next “,” (semicolon) after the found string, and than get the text between the found text and the next semicolon.

    For example, find “, Bl.” and than search for “,” and extract “X5” , valuer between the found string and the next semicolon after the found string. Hope it makes sense 🙂

    It should be a mix of SEARCH and LEFT/LEN/MID , im totally lost …
    Can anyone help me please?

Leave a Reply

Your email address will not be published. Required fields are marked *