Divide one cell text to two.

[Angelos]

Hello all,

I have a list of my customers with their name and surname in the same cell/column. I want to create a column with their names only and another with their surnames only.

I have tried left and if + text formulas, but did not find the proper way.

Can you help please?

Thank you,
Angelos

 

[bosco_yip]

Upload a sample file with 5-10 lines of data together with the expected results.

Regards

 

[Angelos]

There you are.

Thanks.

 

[SirJB7]

Hi, Angelos!

Name:
=ESPACIOS(IZQUIERDA(A2;HALLAR(" ";A2)-1)) -----> in English: =TRIM(LEFT(A2,SEARCH(" ",A2)-1))
Surname:
=ESPACIOS(DERECHA(A2;LARGO(A2)-HALLAR(" ";A2))) -----> in English: =TRIM(RIGHT(A2,LEN(A2)-SEARCH(" ",A2)))

Remember that this works for single names and surnames, i.e., if a person has 2 names o a compound surnames or second name initials, it will only consider the first word as name and the rest as surname.

Regards!

 

[bosco_yip]

1] B2, copy down :

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

2] C2, copy down :

=SUBSTITUTE(A2,B2&" ","")

Regards
Bosco

 

[Angelos]

Thank you both!

 

[bobhc]

What's wrong with a non formula text to column function?

 

[semper5]

What's wrong with a non formula text to column function?

Upload a document showing expected results and we'll see if we can help you.

 

[bobhc]

I dont have a problem, I was say why not use text to function