Extract part of a text

[Faio]

Could anyone help me.

I have a column contains Fullname and I need to separate it to have
Firstname and SecondName in different columns

E.g

John M. Paul so have John M. in one column and Paul is another
column or

Mary Evans it should be Mary in one column and Evans in another
column?

Thanks

 

[Guest]

Hi

One way is to use Data / Text to Columns with a space as the delimiter. This
would split John M. Paul into threee separate columns (and Mary Evans into
2). You can then use CONCATENATE to join the relevant fields (John and M.)
back together.
To do the job with a single formula would be very involved, due to the fact
that some names might have 2 middle initials and others might have none.

 

[Ron Rosenfeld]

Could anyone help me.

I have a column contains Fullname and I need to separate it to have
Firstname and SecondName in different columns

E.g

John M. Paul so have John M. in one column and Paul is another
column or

Mary Evans it should be Mary in one column and Evans in another
column?

Thanks

If lastname always follows the last <space> in the string, then firstname is
given by:

=LEFT(G3,FIND("~",SUBSTITUTE(G3," ","~",LEN(G3)-LEN(SUBSTITUTE(G3," ",""))))-1)

and lastname will be:

=MID(G3,FIND("~",SUBSTITUTE(G3," ","~",LEN(G3)-LEN(SUBSTITUTE(G3,"
",""))))+1,255)

(Both formulas should be all on one line).

If you have suffixes, the exercise becomes much more difficult:

John Paul III

John Paul, Esq

John Paul Jr

etc.
--ron

 

[Faio]

Thanks Ron,

The first one works but with the second one it gives a #value.

 

[Ron Rosenfeld]

Thanks Ron,

The first one works but with the second one it gives a #value.

Probably the line wrapping caused an error. I'll guess that the <space> in the
second SUBSTITUTE function got eliminated.


=MID(G3,FIND("~",SUBSTITUTE(G3," ","~",LEN(G3)-
LEN(SUBSTITUTE(G3," ",""))))+1,255)


--ron

 

[Faio]

Thanks Ron,

It works, after entering it and also adding the ) before the +1.

 

[Ron Rosenfeld]

Thanks Ron,

It works, after entering it and also adding the ) before the +1.

You're welcome. Thanks for the feedback.


--ron