Quick Navigation

## Breaking Down the Reverse String Search Syntax

If you want to understand how the reverse search formula works, or if you need to customize it to look for something other than spaces, look at each line in turn:

=IF(ISERROR(FIND(" ",A1)

The **IF**–**ISERROR** combination looks for an error in the **FIND** function. **FIND** is looking for a space character (” “) inside the string. If it can’t find one, the **IF** statement will be **TRUE**. These lines are to catch the case that the string doesn’t have the character you are looking for.

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

),A1,RIGHT(A1,

Still inside the **IF** function, if there is an error caught by **ISERROR**, the entire formula just returns the original string. Otherwise, we start the reverse **FIND** operation with the **RIGHT** function.

LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")) ) )

This section needs to be read from the inside out to be understood, so we’ll begin with the line with the largest indent.

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

This uses a neat trick to find out how many space characters there are in the string. It does this by using the **SUBSTITUTE** function to remove all the spaces and then compare the length of the string with and without spaces using the **LEN** function. The syntax for the **LEN** and **SUBSTITUTE** functions is as follows:

=LEN(text)

=SUBSTITUTE(text,old_text,new_text,[instance_num])

SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")) )

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

Since we are looking for the final space (we’re searching from right to left, remember), the **SUBSTITUTE** function replaces the last space (which we just found using the **LEN** functions) with a wild character that doesn’t appear anywhere else in the string. In this case we are using a tilde (**~**), but you can change it to any character that won’t be duplicated in the string.

LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")) ) )

Now that the internal functions have replaced the last space character with a tilde, we can find it using a traditional **FIND** function. This entire section is telling the **RIGHT** function how many characters to take from the end of the string, so we need to subtract the location of the last space from the total length of the string.

Test the reverse string formula against our sample string, and it returns “**dog.**” – the final word in the string!

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)))

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)))

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

Check this…

http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba

This is too nice.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ” “, REPT(” “, 99)), 99))

Deepak, that formula worked for me! Thank for posting it.

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.

This one is the one that works for me … amazing, thank you Deepak.

Thanks a lot, Deepak. This is a great help!

Totally worked once I replaced the ” “. Thanks.

I have used this in the past and just used it again. It is a great and simple solution.

Thanks a lot, Andrew. This is a great help!

Nice one, Andrew!

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

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.

Thank you for the excellent sample and explanation.

Was able to easily change to fit my requirements.

Thank you Andrew!!!

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.

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

for extracting text from right before space

Thank you so much! Saved me so much time by using this :)! Great help.

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

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.

Excellent code, worked first time and better than I expected. Thank you so much

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

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))?