Excel: Right Function 6

[rusel01]

Is there away to define the right function to pull all characters until it reaches a space? Like say there's a name in cell A1 that says "John Doe" can it just read the doe and then see the space and stop? Any ideas would be appreaceated.

 

[DaleWatson123321]

This formula will find the first space, and pull out the last name based on the location of the space.

=MID(A1,FIND(" ",A1,1)+1,9999)

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Bowers74]

You can use the following formula:

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

It will return the last part of a cell's text if there is a space separating it and will return the cell's text if no space is found.

The only problem is that it looks for the first instance of a space, so if the cell's text is John Dill Doe the formula will return Dill Doe instead of just Doe

I hope this helps!!!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[KenWright]

If by any chance you were looking to split a bunch of names, then you should also take a look at Data / Text To Columns / Delimited / Space as delimiter

Regards
Ken.............

 

[bluedragon2]

Just because I like creating formulas:

If you have John Doe, or John Dill Doe, this will return just the last name:

=IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))

Blue

 

[DaleWatson123321]

Good one Blue - worthy of a STAR.

...but you should stay away from those "Dill Doe" names

Regards, ...Dale Watson

 

[Bowers74]

I should be the one to apologize for the "Dill Doe" name. That was the name that I used in my worksheet to test my formula.

Sorry!

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[bluedragon2]

Thanks, my mind moves so slow, I usually don't catch things like that

Blue

 

[sfvb]

Catches the last name huh?

Stephen F. Van Buren

 

[KenWright]

Well if you really want just the last name, and it is separated by a space from whatever else is in there:-

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

Regards
Ken..............

 

[sfvb]

Ken,
I think you missed my point. Parsing on a full name doesn't work with people who have a space in their first or last name. That's why I signed with my full name. For example, your code parsed my last name as Buren instead of Van Buren.

Because some people have a space in their first name, and some have a space in their last, parsing a full name can't be done reliably with code alone.

Steve

 

[fneily]

I know some people with the following "endings" - Sr., Jr., II, III, Esq.
And some women with two last names, eg. Alexander Smith. (and they'll let you know if you forget one!)
So now what do you do?

Neil

 

[KenWright]

Sorry, see what you mean. I tried using the code you had been given on your name, and it actually returned 'Van Buren'. Assumed from this you were pointing out that it did not pick up just the last group of characters, and was not what you wanted as it was dependent on how many names were listed. The formula I gave you would do that regardless of how many names there were. You are quite correct though, in that you will never get away from having to do a sanity check on the data afterwards. You could always code in a list of typical last name prefixes such as 'Van' etc and have it adjust to suit, but even then you still need that sanity check.

Regards
Ken............

 

[MenuItem]

I have a similar issue that I'm struggling with.
I'm trying to find a character, "/", starting from the right, and then extract everything BEFORE that character.

How would I do that?

Thanks all.

 

[bluedragon2]

Using Ken's formula in this thread:

=LEFT(A1,FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/","&quot)))-1)

 

[xlbo]

start a new thread menuitem

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[MenuItem]

Considered a new thread, xlbo, but decided that this was pertinent.
After all, it is.
Thanks, BlueDragon2.