Quick Navigation

Excel has some great functions available for working with long phrases (strings). It has **RIGHT**, **LEFT**, and **TRIM** to manage sub-strings. It also has **FIND** and **SEARCH** to look for specific characters or sub-strings inside a phrase. The only problem is that **FIND** and **SEARCH** look from left to right in a string. If you need to look for something starting at the end of a string, there isn’t a good tool built into Excel to do so. Fortunately, it is possible to build a formula to do just that – a reverse string search. Here’s how…

## The Normal FIND Function

Let’s assume we have a simple string of characters we want to manipulate:

The quick brown fox jumps over the lazy dog.

If we were looking to identify the first word in the string, we could use a basic **FIND** function. The syntax for **FIND** is as follows:

=FIND(find_text,within_text,[start_num])

Assuming our string is in cell **A1**, the **FIND** function that locates the first space in the sentence is as follows:

=FIND(" ",A1)

The function returns **4**. To get the first word in the sentence, we can use a **LEFT** function. The syntax for the **LEFT** function is as follows:

=LEFT(text,[num_chars])

The **LEFT** function that gives us the first word is as follows:

=LEFT(A1,FIND(" ",A1)-1)

It returns **The**. But what if we want the last word in the sentence? For that, we need to reverse the **FIND** function…

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.

This worked for me too!! Thanks, this is awesome!!

Depak – this just worked for me too! Thanks

There is an even easier way:

=RIGHT(A1,FINDrev(” “,A1))

All you need is to define a super-simple VBA user-defined function FINDrev(), which is essentially the standard FIND() function, but working from right to left:

Public Function FINDrev(Find_text As String, Within_text As String)

FINDrev = Len(Within_text)-Len(Find_text)-InStrRev(Within_text, Find_text)+1

End Function

Your recommended approach has saved me a lot of time in what otherwise would have been an horrendous task. Also, your post introduced me to creating user defined functions. This is something I didn’t previously know about, so thank you VaskoG for sharing your knowledge as you have done. It has been very much appreciated.

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

You can also use =RIGHT(A1,LEN(A1)-FIND(” “,A1)-1)

Brilliant work! Kudos to the developer of this logic

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?

Use text to columns button with the delimiter of ,

If anyone wants the inverse of this (everything except last name), here is an extremely complicated formula to get that (note, you can do a find and replace of H3 on the formula cell to change to whatever cell you want): =LEFT(H3,LEN(H3)-LEN(IF( ISERROR(FIND(” “,H3)),H3,RIGHT(H3,LEN(H3)-FIND(“~”,SUBSTITUTE(H3,” “,”~”,LEN(H3)-LEN(SUBSTITUTE(H3,” “,””)))))))-1)

This worked would well for me – essentially using the FIND function after reversing the string.

=IFERROR(RIGHT(A1,IFERROR(FIND(“,”,TEXTJOIN(“”,1,MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1))),0)-2),A1)

This is why I prefer to do data manipulation in MS Access vs. Excel. INSTREV is a built-in function in the query builder in Access, so you can just use it right in a calculated field of the query.

The other example in the comments above that uses VBA also taps into INSTREV.

MS just needs to expose it as a native function in the Excel sheet context and many things would be easier.

I got a suggestion from another page to use the “substitute”. I needed to get the first three octets of a TCP/IP address and needed a similar response, but LEFT part based on the 3rd “period”.

Assuming the column of IP’s I’ve captured all use a /24:

Cell B1:

=CONCATENATE(LEFT(SUBSTITUTE(A1,”.”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”.”,””))),FIND(“*”,SUBSTITUTE(A1,”.”,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,”.”,””))),1)-1),”.0/24″)

Where A1 = 192.168.12.254

Returns: 192.168.12.0/24

Worked perfectly!