How can I perform a reverse string search in Excel without using VBA?

Question

I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.

Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?

Examples:

  Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
                  Aziz! Light! -> Light!

I'm wondering why you have the artificial no VBA constraint? — EBGreen, Dec 08 '08 at 17:41
I can easily solve it with VBA, but I'm curious if there is a non-VBA solution. VBA tends to have performance penalties for large data sets. — e.James, Dec 08 '08 at 18:20
As usual, two answers really stand out, and I have a tough time deciding which one to select as the correct answer. In this case, both Jon and BradC (with the help of Brad) have come up with correct, working solutions. — e.James, Dec 08 '08 at 18:34
I have selected BradC's solution because it seems to be the more elegant of the two, and he provides a handy explanation of the function. — e.James, Dec 08 '08 at 18:35
It's hard to answer your question properly if you don't indicate what makes VBA inappropriate (since you can write your own macros and functions in VBA, making it equivalent to the built-in functions). — dkretz, Jan 27 '11 at 12:19
One thing I will say, is that the reason it is better to use formulae over VBA is because you stay a .xslx. Once you write a line of VBA, you become a .xlsm and a potential security threat that could write to any COM port, registry; open applications; yada yada - VBA is integrated throughout windows - to any virus checker, email client; or application that tries to open it. So, staying in "spreadsheet land" adds a lot of value to the average "security conscious user". That's why Lamdba functions are so powerful, they bring you a step closer to VBA without making your doc a security threat. — Mike Parks, Apr 29 '21 at 10:05
With functions you have just processed the data - however "elegantly" - with vba you could have reaped "the whirlwind" or created "paradise". Which you chose: was up to you. Sometimes, "keeping it simple" is the most elegant solution of all. — Mike Parks, Apr 29 '21 at 11:03
and further to @MikeParks, O365 can't cope with VBA if you're using web excel or teams excel — northern-bradley, Nov 30 '22 at 17:32
Too bad this couldn't be as simple as in PHP, where if you give it a negative start point it searches backwards. — Elliptical view, Jan 08 '23 at 18:25

score 233 · Accepted Answer · edited Sep 07 '12 at 12:59

233

This one is tested and does work (based on Brad's original post):

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",
     LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

If your original strings could contain a pipe "|" character, then replace both in the above with some other character that won't appear in your source. (I suspect Brad's original was broken because an unprintable character was removed in the translation).

Bonus: How it works (from right to left):

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) – Count of spaces in the original string
SUBSTITUTE(A1," ","|", ... ) – Replaces just the final space with a |
FIND("|", ... ) – Finds the absolute position of that replaced | (that was the final space)
Right(A1,LEN(A1) - ... )) – Returns all characters after that |

EDIT: to account for the case where the source text contains no spaces, add the following to the beginning of the formula:

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

making the entire formula now:

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

Or you can use the =IF(COUNTIF(A1,"* *") syntax of the other version.

When the original string might contain a space at the last position add a trim function while counting all the spaces: Making the function the following:

=IF(ISERROR(FIND(" ",B2)),B2, RIGHT(B2,LEN(B2) - FIND("|",
    SUBSTITUTE(B2," ","|",LEN(TRIM(B2))-LEN(SUBSTITUTE(B2," ",""))))))

edited Sep 07 '12 at 12:59

Markus Jarderot

86,735
21
136
138

answered Dec 08 '08 at 18:27

BradC

39,306
13
73
89

1

Cannot thank you enough. Needed the rest of the string too, so simply changing =RIGHT to =LEFT and removing LEN(A1)- allowed me to get the rest. But thank you for doing all the initial leg work :) – Luke Duddridge Jun 09 '10 at 08:25
4

+1: I'll have to remember the "LEN(A1)-LEN(SUBSTITUTE(A1," ",""))" trick for getting the number of instances of a character – anschauung Mar 28 '11 at 18:41
2

Excellent solution! The only problem is that Excel localizes function names, so you need to re-write the formula for the non-English Excel. Here is the re-written variant for the Russian one: =ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК("|";ПОДСТАВИТЬ(A1;" ";"|";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))) – Michael Pliskin Nov 28 '11 at 13:13
This answer breaks on strings containing consecutive spaces. – somewhatsapient Feb 27 '13 at 20:42
1

I would recommend breaking up the formulas into temporary columns (you can hide them later). It's very useful for debugging special cases. – wisbucky May 07 '14 at 20:14
If Someone needs the solution in German: =RECHTS(A1;LÄNGE(A1)-FINDEN("#";WECHSELN(A1;" ";"#";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";""))))) – Ephedra Oct 29 '15 at 08:59
And here is the Portuguese version: =DIREITA(A1;NÚM.CARACT(A1)-PROCURAR("|";SUBSTITUIR(A1;" ";"|"; NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1;" ";""))))) – Caverna May 18 '18 at 14:37
to put [@marcelo's translation for PT-BR](https://stackoverflow.com/a/1313566/3501748) with the ones above `=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|",SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))` – northern-bradley Nov 30 '22 at 17:39

score 94 · Answer 2 · edited Jul 13 '12 at 16:45

94

This is the technique I've used with great success:

=TRIM(RIGHT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

To get the first word in a string, just change from RIGHT to LEFT

=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 100)), 100))

Also, replace A1 by the cell holding the text.

edited Jul 13 '12 at 16:45

Community

1
1

answered Mar 02 '12 at 01:31

Jerry Beaucaire

3,133
16
14

2

Works for me - just replaced the first `" "` with my delimiter. The two `100`s seem to limit it to a string of 100 characters if I'm not mistaken – Benjineer Jan 22 '14 at 05:14
2

If we use a different delimiter we need to strip that out at the end too, e.g. `=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1, ".", REPT(".", 100)), 100), ".", ""))` – dumbledad Jul 31 '15 at 08:44
9

Very clever method. What it does is replace every space with 100 spaces, then return the last 100 characters without leading/trailing spaces. – Zenadix Aug 21 '15 at 19:24
@Benjineer I think it works for strings much longer than 100 characters. The 100 characters limits the size of a single word. You're taking the right (or left) hundred characters ..... if a word of 101 characters long you'd have a problem, but if a string overall of 100 characters ... with 3 spaces ... would be padded to approx a 400 character string and still work. To Jerry Beaucaire - Very elegant, thanks. – Tin Bum Feb 22 '19 at 23:49

score 28 · Answer 3 · answered Jun 01 '13 at 01:03

28

A more robust version of Jerry's answer:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))

That works regardless of the length of the string, leading or trailing spaces, or whatever else and it's still pretty short and simple.

answered Jun 01 '13 at 01:03

Joe Finkle

281
3
3

3

It's an excellent formula. I modified the formula to `=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), ".", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1))))` to get file extension. – Adarsh Madrecha May 30 '16 at 09:47
2

One more `SUBSTITUTE` allows it to work for any character, not just spaces. `=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(TRIM(A1),"/",REPT("/",LEN(TRIM(A1)))),LEN(TRIM(A1))),"/",""))`, where "/" is the delimiting character. – PProteus Dec 07 '18 at 19:53
@Joefinkle, this is a really concise and smart solution. It works pretty well, just to mention that if the delimiter is a string it does not work well in all cases, here an example for a delimiter: `" ; "` (a semi-colon surrounded by spaces) with input value: `"Technology Sprint 24 ; Sprint 4"` it returns: `"; Sprint 4"`. I am using the solution addapted by: @PProteus. – David Leal Jul 01 '19 at 20:24
If the delimiter is a string a possible workaround would be to replace it before by some special character, for example replacing in the @PProteus formula `TRIM(A1)` by: `TRIM(SUBSTITUTE(A1, "strDelimeter", ";"))` in all places, in order to have a new character delimiter: `";"` or even better using `char()` function to find some really unexpected character. – David Leal Jul 01 '19 at 20:46
2

@PProteus you don't need the `TRIM` function after adding the second `SUBSTITUTE` for the general case single character solution. The formula provides the expected result eliminating this part: `=SUBSTITUTE(RIGHT(SUBSTITUTE(TRIM(A1),";",REPT(";",LEN(TRIM(A1)))),LEN(TRIM(A1))),";","")` – David Leal Jul 01 '19 at 21:01

Jon · Answer 4 · 2008-12-08T18:44:58.857

13

I found this on google, tested in Excel 2003 & it works for me:

=IF(COUNTIF(A1,"* *"),RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND(" ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))))),A1)

[edit] I don't have enough rep to comment, so this seems the best place...BradC's answer also doesn't work with trailing spaces or empty cells...
[2nd edit] actually, it doesn't work for single words either...

edited Dec 08 '08 at 18:44

answered Dec 08 '08 at 18:12

Jon

16,212
8
50
62

Closest solution yet, but I would throw in a Trim() since a trailing space will break it. – EBGreen Dec 08 '08 at 18:15
True, probably easiest to trim() in an intermediate cell & then apply above formula to the intermediate cell. Also, spits out 0 if the cell happens to be empty, so could also be wrapped with isblank() – Jon Dec 08 '08 at 18:22

score 4 · Answer 5 · answered Sep 28 '22 at 13:59

4

New answer 9/28/2022

Considering the new excel function: TEXTAFTER (check availability) you can achieve it with a simple formula:

=TEXTAFTER(A1," ", -1)

answered Sep 28 '22 at 13:59

David Leal

6,373
4
29
56

1

Thank you! Didn't know this function existed. – Jonathan Jul 07 '23 at 07:52

score 3 · Answer 6 · edited May 12 '12 at 09:20

3

=RIGHT(A1,LEN(A1)-FIND("`*`",SUBSTITUTE(A1," ","`*`",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

edited May 12 '12 at 09:20

Cody Gray - on strike

239,200
50
490
574

answered Dec 08 '08 at 17:52

Did you test it? Doesn't work for me with "Are you classified as human?" – Ed Guiness Dec 08 '08 at 17:55
No, it does not work. Has some interesting elements, though. LEN(A1)-LEN(SUBSTITUTE(A1," ","") gives you the count of spaces in the string. – BradC Dec 08 '08 at 18:04
That is an interesting function. Too bad it doesn't work. I like the trick for being able to count the number of spaces. – e.James Dec 08 '08 at 18:24
@Brad: I edited your post to show the * characters. The original did not print them properly. With these in place, it does work. +1 – e.James Dec 08 '08 at 18:38

score 3 · Answer 7 · edited May 12 '12 at 09:20

3

This is very clean and compact, and works well.

{=RIGHT(A1,LEN(A1)-MAX(IF(MID(A1,ROW(1:999),1)=" ",ROW(1:999),0)))}

It does not error trap for no spaces or one word, but that's easy to add.

Edit:
This handles trailing spaces, single word, and empty cell scenarios. I have not found a way to break it.

{=RIGHT(TRIM(A1),LEN(TRIM(A1))-MAX(IF(MID(TRIM(A1),ROW($1:$999),1)=" ",ROW($1:$999),0)))}

edited May 12 '12 at 09:20

Cody Gray - on strike

239,200
50
490
574

answered Oct 27 '10 at 01:00

gabrielu

11
2

Cool. That is a really interesting use of array functions! I didn't expect to get another answer after such a long time. Thank you for sharing. – e.James Oct 27 '10 at 01:21

score 3 · Answer 8 · edited Jan 06 '12 at 17:02

3

=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(7),SUBSTITUTE(" "&TRIM(A1)," ",CHAR(7),
LEN(TRIM(A1))-LEN(SUBSTITUTE(" "&TRIM(A1)," ",""))+1))+1)

This is very robust--it works for sentences with no spaces, leading/trailing spaces, multiple spaces, multiple leading/trailing spaces... and I used char(7) for the delimiter rather than the vertical bar "|" just in case that is a desired text item.

edited Jan 06 '12 at 17:02

Devin Burke

13,642
12
55
82

answered May 13 '11 at 13:41

Mark Main

31
1

Works for me in excel 2010. Thanks – Rudiger Wolf Jun 29 '12 at 05:16

score 2 · Answer 9 · answered Jul 24 '13 at 07:22

To add to Jerry and Joe's answers, if you're wanting to find the text BEFORE the last word you can use:

=TRIM(LEFT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))))-LEN(TRIM(A1))))

With 'My little cat' in A1 would result in 'My little' (where Joe and Jerry's would give 'cat'

In the same way that Jerry and Joe isolate the last word, this then just gets everything to the left of that (then trims it back)

score 2 · Answer 10 · answered Jun 08 '17 at 03:47

2

Copy into a column, select that column and HOME > Editing > Find & Select, Replace:

Replace All.

There is a space after the asterisk.

answered Jun 08 '17 at 03:47

pnuts

58,317
11
87
139

Note that * is a (greedy) wildcard in excel searches, so this matches everything up to the last space and replaces it with nothing – Superfly Jon Aug 08 '19 at 09:55

score 1 · Answer 11 · edited Jul 01 '16 at 12:50

1

=LEFT(A1,FIND(IF(
 ISERROR(
  FIND("_",A1)
 ),A1,RIGHT(A1,
  LEN(A1)-FIND("~",
   SUBSTITUTE(A1,"_","~",
    LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))
   )
  )
 )
),A1,1)-2)

edited Jul 01 '16 at 12:50

mathielo

6,725
7
50
63

answered Jul 01 '16 at 12:33

J.B.

445
1
4
8

score 1 · Answer 12 · edited Dec 01 '12 at 17:02

Imagine the string could be reversed. Then it is really easy. Instead of working on the string:

"My little cat" (1)

you work with

"tac elttil yM" (2)

With =LEFT(A1;FIND(" ";A1)-1) in A2 you get "My" with (1) and "tac" with (2), which is reversed "cat", the last word in (1).

There are a few VBAs around to reverse a string. I prefer the public VBA function ReverseString.

Install the above as described. Then with your string in A1, e.g., "My little cat" and this function in A2:

=ReverseString(LEFT(ReverseString(A1);IF(ISERROR(FIND(" ";A1));
  LEN(A1);(FIND(" ";ReverseString(A1))-1))))

you'll see "cat" in A2.

The method above assumes that words are separated by blanks. The IF clause is for cells containing single words = no blanks in cell. Note: TRIM and CLEAN the original string are useful as well. In principle it reverses the whole string from A1 and simply finds the first blank in the reversed string which is next to the last (reversed) word (i.e., "tac "). LEFT picks this word and another string reversal reconstitutes the original order of the word (" cat"). The -1 at the end of the FIND statement removes the blank.

The idea is that it is easy to extract the first(!) word in a string with LEFT and FINDing the first blank. However, for the last(!) word the RIGHT function is the wrong choice when you try to do that because unfortunately FIND does not have a flag for the direction you want to analyse your string.

Therefore the whole string is simply reversed. LEFT and FIND work as normal but the extracted string is reversed. But his is no big deal once you know how to reverse a string. The first ReverseString statement in the formula does this job.

That is so much simpler than all the other options here, thanks so much!! Why Find can't have a reverse find, or substitute - I seem to remember something to do with using a negative number to do reverse search, but clearly not vba.... — Craig Lambie, Oct 05 '17 at 15:08
Also thats insane! If you are going to use VBA you could just as easily have a reverse find — Denzil Newman, Feb 28 '19 at 16:18

score 0 · Answer 13 · edited May 12 '12 at 09:20

0

I translated to PT-BR, as I needed this as well.

(Please note that I've changed the space to \ because I needed the filename only of path strings.)

=SE(ÉERRO(PROCURAR("\",A1)),A1,DIREITA(A1,NÚM.CARACT(A1)-PROCURAR("|", SUBSTITUIR(A1,"\","|",NÚM.CARACT(A1)-NÚM.CARACT(SUBSTITUIR(A1,"\",""))))))

edited May 12 '12 at 09:20

Cody Gray - on strike

239,200
50
490
574

answered Aug 21 '09 at 18:29

Marcelo

123
6

3

I don't know how to read Excel in Portuguese, so I'll have to take your word for it :) – e.James Aug 21 '09 at 19:06

score 0 · Answer 14 · answered Jul 13 '16 at 09:35

Another way to achieve this is as below

=IF(ISERROR(TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14))))),TRIM(D14),TRIM(MID(TRIM(D14),SEARCH("|",SUBSTITUTE(TRIM(D14)," ","|",LEN(TRIM(D14))-LEN(SUBSTITUTE(TRIM(D14)," ","")))),LEN(TRIM(D14)))))

score 0 · Answer 15 · answered May 27 '21 at 10:49

You can achieve this also by reversing the string and finding the first space

=MID(C3,2+LEN(C3)-SEARCH(" ",CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN(C3),-1),1))),LEN(A1))

Reverse the string

CONCAT(MID(C3,SEQUENCE(LEN(C3),,LEN(C3),-1),1))

Find the first space in the reversed string

SEARCH(" ",...

Take the position of the space found in the reversed string off the length of the string and return that portion

=MID(C3,2+LEN(C3)-SEARCH...

score -1 · Answer 16 · edited Oct 18 '12 at 17:39

-1

I also had a task like this and when I was done, using the above method, a new method occured to me: Why don't you do this:

Reverse the string ("string one" becomes "eno gnirts").
Use the good old Find (which is hardcoded for left-to-right).
Reverse it into readable string again.

How does this sound?

edited Oct 18 '12 at 17:39

Alfabravo

7,493
6
46
82

answered Oct 18 '12 at 17:38

Tod Heartsound

7
1

13

Sure. But how do you reverse the string in Excel? – e.James Oct 18 '12 at 23:42

How can I perform a reverse string search in Excel without using VBA?

16 Answers16

New answer 9/28/2022

Linked