Separating Names & Last Names

[Fernando Gomez]

I have a long list of Las Names and Names coming from a database, in this
way
Russel...Mark (the dot comes in different numbers from 2 to 6), what I am
trying to do is with a formula, separate last name and put that in a column
& name put in a different column.
A solution was given to use Text to column with delimiter as ".", but
unfortunately, about 25 % of lines is not separated why I do not know, 2
lines could have the same number of characters and same number of "."s, and
one is not separated. does somebody knows another solutionwith a formula.

Thanks

 

[Stephen Bullen]

Hi Fernando,

I have a long list of Las Names and Names coming from a database, in this
way
Russel...Mark (the dot comes in different numbers from 2 to 6), what I am
trying to do is with a formula, separate last name and put that in a column
& name put in a different column.
A solution was given to use Text to column with delimiter as ".", but
unfortunately, about 25 % of lines is not separated why I do not know, 2
lines could have the same number of characters and same number of "."s, and
one is not separated. does somebody knows another solutionwith a formula.

If A1 contains the full string, then you can get the left-hand part using:
=LEFT(A1,FIND(".",A1)-1)

and the right-hand part using:
=SUBSTITUTE(MID(A1,FIND(".",A1),100),".","")

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk