Formular to extract names

[[email protected]]

Hello All,

Good day. In the attached, I have a list of names. I need a formula to extract the surnames only, the middle names only and the first names only as indicated in the headings.

Any clue?

Regards.

[jeffreybrown]

Try in B2 copied across and down...

=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",99)),COLUMNS($A:A)*99,99))

Note: This will do First Name, Middle Name, and then Surname. Just change your headers to reflect

[johnmpl]

Hi, to both!

A little shorter could be:
[B2] : =TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",99)),1+99*(COLUMNS(B2:$D2)-1),99))
Drag it down and right.

Blessings!

[63falcondude]

Here is an alternative if you want 3 separate formulas.

Assuming that the surname is at the end, the middle name is in the middle, and the first name is in the beginning, you can use these 3 formulas:

B2 =MID(A2,FIND("|",SUBSTITUTE(A2," ","|",2))+1,LEN(A2))

C2 =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100))

D2 =LEFT(A2,FIND(" ",A2)-1)