Excel 2007 Formula 1

[kristal9]

Hi...

I need to match a set of order data that contains the sales person to a Hierarchy table where to pull back the sales persons' Manager and VP. The challenge is the sales person name in the data table is FirstName LastNam and the sales person name in the Hierarchy table is LastName, FirstName.

Do anyone have a formula that can take "Bob Jones" and convert it to "Jones, Bob" ? So that afterwards I can do a vlookup...

I know it won't be 100% clean as sometimes the name includes a middle initial but if I can knock out the majority of them with a formula I don't mind manually scrubbing the rest.

Any suggestions?
Thanks!

 

[SkipVought]

Hi,

Why not bite the bullet and store First and Last in a separate column. Use Text to columns, DELIMIT on COMMA and SPACE.

Is EVERY first one ONE WORK and EVERY last name ONE WORD, in EACH AND EVERY INSTANCE? You can use the FIND function to locate the COMMA or SPACE (whichever you want to reconfigure) and then the LEFT and RIGHT & LEN functions to parse and reconfigure...


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

I wholeheartedly agree with Skip about storing the name-parts separately. Also, I'd suggest having Employee IDs that can be searched against instead of relying on names.

As far as a formula goes, try this:
[tab]=Right(A1, Len(A1) - Search(" ", A1)) & ", " & Left(A1, Search(" ", A1) - 1)



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.