Nesting "RIGHT", "MID", and "FIND" in a formula

 I am trying to get the correct formula to extract the last word in a string.

Data                                                              Correct Answer

John Doe                                                          Doe

John A. Downer                                                Downer

John A. and Jane Smithhaven                          Smithhaven

If anyone can give me the correct fomula for extracting this data I would greatly appreciate it.

Gary

I always use a UDF (User Defined Function) like that below.

Public Function FindLastSpace(sInput As String) As String
Dim iLastSpacePos As Integer
Dim iLengthOfString As Integer

Application.Volatile True

iLastSpacePos = InStrRev(sInput, " ", -1)
iLengthOfString = Len(sInput)
FindLastSpace = Mid(sInput, iLastSpacePos + 1, iLengthOfString - (iLastSpacePos - 1))
End Function

Sans VBA

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,99)

Bob

That was why I went avec VBA. Wish I could take the time to sit and work out some of these great function combinations!

 Bob, since I'm not a Visual Basics guy, thanks for the formula.  I've been working on this for over two days and I never would have figured it out.

Thanks again.........

I'll give you a quick synopsis Gary to help you develop your own skills.

The key problem is finding out where the last space is, or how many spaces there are, and this is done by looking at the length of the cell value if it didn't have any spaces in it ( the LEN(SUBSTITUTE(A1," ","")) ...) and taking that figure away from the length of the whole cell ( LEN(A1)-LEN(SUBSTITUTE(A1," ","")) ...). So for John Doe we get 1, John A.Downer 2, John A. and Jane Smithhaven 4).

We can then use SUBSTITUTE again to replace a paricular instance of a string with another, in other words, replace the final space with a non-used character, I use CHAR(1). So SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))) changes just the last space.

We then use FIND to determine the location of that unused character, FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))) so we know where to extract the data from.

And finally, we use MID to get that last space +1 as our start point, and I use 99 as the length because MID does not choke if the length is too long, so we get away with a largeish arbitrary number, MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,99)

Hope that helps.

BTW, let that be a lesson to you . NEVER work on something in Excel for 2 days without getting anywhere; ask for help, a pointer, or a suggestion, time is too precious.

Bob

Thanks for setting that precedent, can we expect clear concise explanations of your solutions each time in future

Great job!

Nick,

You know that normally my formulae are totally obvious, so they self-document, and need no further explanation.

Bob

I have an instant use for the formulae in this thread, but had to add TRIM around each cell refernce as the data comes from an iSeries and has fixed width padding. That done it worked great.

Could have used ny UDF or course, but I'm sure yours would be faster on 25,000+ records

Serendipity.

Also sans VBA, but with the following ancillary overhead: a very hidden worksheet named HiddenWorksheet and defined names NCHARS referring to 255 (or some other biggish integer < 32768) and seq referring to

=ROW(INDEX(HiddenWorksheet!$1:$65536,1,1):INDEX(HiddenWorksheet!$1:$65536,NCHARS,1))

use the formula

=MID(A1,LOOKUP(2,1/(MID(A1,seq,1)=" "),seq)+1,NCHARS)

But a better solution, if allowed, would be to use Longre's MOREFUNC.XLL add-in and formulas like

=WMID(A1,WORDCOUNT(A1))

 This improves on previous posts (in my opinion) because it does not have an arbitrary limit of 99 (or any) characters:

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

What it's doing is finding the total number of spaces in the cell by subtracting the

length without spaces from the total length. Knowing that number, it uses it to replace the last space with an asterisk. From that point, it's easy; just take everything to the right of the asterisk.  NOTE: If you have asterisks in your text, you can change the function to use tildes (~) or back apostrophies (`)

Courtesy of:

http://www.exceltip.com/st/Extract_the_last_word_in_a_cell_in_Microsoft_Excel/368.html

SeanLeal:

This improves on previous posts (in my opinion) because it does not have an arbitrary limit of 99 (or any) characters:

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

Oh, so colorful!

Let's consider whether this is, in fact, an improvement. Like all SUBSTITUTE approaches, it requires the use of an indicator character to give the location of the particular character sought. FAR BETTER to use a control/non-graphic character, such as CHAR(1) which Bob Phillips used, because such characters are far less likely to appear in arbitrary strings than any graphic characters.

Next, MID with 3rd argument 32767 will always be more efficient then RIGHT formulas because they need fewer function calls. And since strings can never exceed 32767 characters, there's no arbitrary limit either. It's a pity Excel's MID's 3rd argument isn't optional like VBA's Mid function.

As you can see from my creative color scheme, clarity is paramount when I consider the 'best' option.  As someone who supports many levels of Excel users I need to be able to clearly explain what a formula does, preferably in English.  It also has to be clear enough for basic level users to modify it for their uses.  (The colors help.)

I don't doubt your solution would run faster, and I agree char(1) is a much safer replacement character, but it's much easier to explain * or ~ to an administrative assistant who just needs to pull out the last name of their manager's contact list.

... Which is why it is an improvement for me.