Recognising how data is formatted in a cell / recognising how a name is formatted in a cell

[D_R_D]

Hi,

I've got a list of names in Column A, e.g.
Joe Bloggs
John M. Bloggs
Mary-Ann Bloggs
Ruth Sally Bloggs

All the names are in different formats (i.e. some just first name and last name, some with a middle initial included, some with double-barrel first names, etc.)

I know how to isolate the first name in Column B and isolate the last name in Column C for each one using LEFT and RIGHT formulas, but because the names in Column A are in all different formats, I'm not able to copy down these formulas effectively in Columns B and C. I guess I have to use an IF function to say, for example for the Last Name, "

=IF(A2 [is in format 1], [enter the formula that works for that format type], IF(A2 [is in format 2], [enter the formula that works for that format type], IF(A2 [is in format 3... etc."

The part I don't know how to do is this part: "[is in format 1]", "[is in format 2]", etc.

Could you please let me know if it is possible to achieve this using just formulas (no VBA or Macros please)

Thanks & regards
David

 

[Marcílio_Lobão]

D_R_D, Good afternoon.

I believe that's not necessary to create differents formats rules.
Maybe a standart formula may solve the question.

Remember that using Excel there is always a new way to solve problems.

My Way
Scenario: A2 --> Original Name

Try to use:

B2 --> =LEFT($A2,SEARCH(" ",$A2,1)-1)

C2 --> =IF(LEN($A2)=LEN($B2&" "&$D2),"",MID($A2, LEN($B2)+2, LEN($A2) - (LEN($B2)+1) - LEN($D2)-1))

D2 --> =TRIM(RIGHT(SUBSTITUTE($A2," ",REPT(" ",255)),255*1))

Please, tell us if it worked as you want.

I hope it helps.

 

[D_R_D]

Thank you so much Marcílio!

This will work perfectly!

Could I ask you a follow-up question please? What would you suggest if the list of names looks contains a mixture like this, where the name might begin with the first name, or it might begin with the last name:

Joe Bloggs
John M. Bloggs
Mary-Ann Bloggs
Ruth Sally Bloggs
Bloggs, Jack --*note the comma is included after the last name, but also that the last name appears first
Bloggs Betty --*note that this time the last name appears first but there is no comma after it


Thanks & regards,
David