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!

User · Answer

LEFT A1 FIND IF   ISERROR    FIND     A1     A1 RIGHT A1    LEN A1 -FIND         SUBSTITUTE A1              LEN A1 -LEN SUBSTITUTE A1                        A1 1 -2

User · Answer

RIGHT A1 LEN A1 -FIND       SUBSTITUTE A1           LEN A1 -LEN SUBSTITUTE A1

User · Answer

RIGHT TRIM A1  LEN TRIM A1  -FIND CHAR 7  SUBSTITUTE     amp TRIM A1      CHAR 7   LEN TRIM A1  -LEN SUBSTITUTE     amp 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

User · Answer

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           ndash  Count of spaces in the original string SUBSTITUTE A1                 ndash  Replaces just the final space with a   FIND             ndash  Finds the absolute position of that replaced    that was the final space  Right A1 LEN A1  -         ndash  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

User · Answer

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

User · Answer

RIGHT A1 LEN A1 -FIND       SUBSTITUTE A1           LEN A1 -LEN SUBSTITUTE A1

User · Answer

RIGHT TRIM A1  LEN TRIM A1  -FIND CHAR 7  SUBSTITUTE     amp TRIM A1      CHAR 7   LEN TRIM A1  -LEN SUBSTITUTE     amp 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

User · Answer

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

User · Answer

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

User · Answer

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           ndash  Count of spaces in the original string SUBSTITUTE A1                 ndash  Replaces just the final space with a   FIND             ndash  Finds the absolute position of that replaced    that was the final space  Right A1 LEN A1  -         ndash  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

User · Answer

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           ndash  Count of spaces in the original string SUBSTITUTE A1                 ndash  Replaces just the final space with a   FIND             ndash  Finds the absolute position of that replaced    that was the final space  Right A1 LEN A1  -         ndash  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

LEFT A1 FIND IF   ISERROR    FIND     A1     A1 RIGHT A1    LEN A1 -FIND         SUBSTITUTE A1              LEN A1 -LEN SUBSTITUTE A1                        A1 1 -2

User · Answer

I found this on google  tested in Excel 2003  amp  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

User · Answer

I found this on google  tested in Excel 2003  amp  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

User · Answer

Copy into a column  select that column and HOME   Editing   Find  amp  Select  Replace     Replace All   There is a space after the asterisk

User · Answer

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?

User · Answer

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

User · Answer

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?

User · Answer

RIGHT A1 LEN A1 -FIND       SUBSTITUTE A1           LEN A1 -LEN SUBSTITUTE A1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Copy into a column  select that column and HOME   Editing   Find  amp  Select  Replace     Replace All   There is a space after the asterisk

User · Answer

I found this on google  tested in Excel 2003  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I found this on google  tested in Excel 2003  amp  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

User · Answer

RIGHT A1 LEN A1 -FIND       SUBSTITUTE A1           LEN A1 -LEN SUBSTITUTE A1

User · Answer

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           ndash  Count of spaces in the original string SUBSTITUTE A1                 ndash  Replaces just the final space with a   FIND             ndash  Finds the absolute position of that replaced    that was the final space  Right A1 LEN A1  -         ndash  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

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

The answer is

Examples related to excel

Examples related to excel-formula

Tags