Excel lookup formula

[voltarei]

Hi Guys,
I'm looking for one of two solutions.

I have a spreadsheet that has peoples names in column A, but this one cell contains the whole name (Mr Bob Jones).
I need to remove the "MR" part of the name, so I can conduct a vlookup on just the name and surname.
I've tried doing a text to columns, but this either puts all parts of the name into separate columns (which is just as useless), or if I do it by fixed width, it cuts of certain characters as "MR" is shorted than "Miss" etc..

How can I convert this cell with the whole name into a cell that doesn't contain the "Mr" part?
Or
Can I enter a formula that will look at A1 and check if certain text appears in D1, then add a "yes" to E1 etc..?

Any help really appreciated.

Cheers
Phil

 

[SkipVought]

hi,

but this either puts all parts of the name into separate columns (which is just as useless),

WHY? That should be EXACTLY what you need? If need be, you can concatenate values as required.

Please explain EXACTLY why this would not work along with supporting examples of your lookup table and formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

How can I convert this cell with the whole name into a cell that doesn't contain the "Mr" part?:
=MID(A1,FIND(" ",A1)+1,99)
Then Copy,PasteSpecial to values

Gavin

 

[xlhelp]

I believe the OP is asking for a solution when only some of the cells have Mr or Miss etc, but the others do not. Skip is very right, but Text to Columns will not yield desired result. Gavon's formula is really good, but Bob Smith will only yield Smith and get rid of Bob.

There may be a shorter version of what I have below. The formula will get rid of any title that is included with the name:

=IF(OR(LEFT(A2,FIND(" ",A2))= "Mr ",LEFT(A2,FIND(" ",A2))="Miss ",LEFT(A2,FIND(" ",A2))="Ms ",LEFT(A2,FIND(" ",A2))="Mrs "),MID(A2,FIND(" ",A2)+1,99),A2)

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[macropod]

Hi Phil,

Is there a reason you can't simply use Find/Replace to delete the 'Mr' etc strings?

Cheers
Paul Edstein
[MS MVP - Word]

 

[Gavona]

There may be a solution to the original problem using wildcards within the vlookup. However I am not sure if it works the wrong way round for the op's problem. thread68-1253790. Had that thread about wildcards in my archive for a while - thanks Ken!
=VLOOKUP("*"&D2,mylist,1,0)
This will lookup the name without salutation in the named range "mylist" where mylist does include the salutation.


Find/Replace (or the Substitute function) depends on having a complete list of salutations (this could be quite long - Dr, Lt. Sgt. Sir....). If you have this then I would probably use a macro to run through it and do multpile find/replace operations. However if you prefer a formula approach then:
You could use the left function to find the first "word" in the cell.
Do a vlookup on this to a list of salutations.
If the lookup fails then retain the original value otherwise use my original formula.
Something like (untested):
=if(isna(vlookup(left(A2,find(" ",A2)-1,SalutationList,1,0),A2,=MID(A2,FIND(" ",A2)+1,99))

Come to that just lookup twice - with and without the first word:
=if(isna(vlookup(A2,MasterList,1,0),
vlookup(MID(A2,FIND(" ",A2)+1,99),MasterList,1,0),
vlookup(A2,MasterList,1,0))



Gavin

 

[voltarei]

Thanks everyone - xlhelp's formula works a treat.
I'd never even thought about find and replace macropod, but will try this too as it may be something to remember for future.

Thanks
Phil

 

[lionelhill]

Dr, Mme, Mlle, Frau, Herr, Prof., Master, Rev., His Honour, Her Majesty??

(In future, it'd be better to keep the parts of a name separate from the word go...)