Extract part of a text

  • Thread starter Thread starter Faio
  • Start date Start date
F

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
 
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.
 
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
Click to expand...

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
 
Thanks Ron,

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

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
 
Back
Top