How to split up the data in one column

T

TxWebdesigner

Hi,

I have a long list of full names, Spanish names at that, that need to be
separated into First Name and Last Name columns.

Sometimes, there are 2 or 3 or 4 words to the names so the first thing I
really need to do is figure out how to sort these so that all the names with
2 words are grouped and all the names with 3 words are grouped and so on so
that I can then try to separate them out.

Sample:

ACUÑA BONILLA ARMANDO
ACUÑA CASTRO CARLOS MANUEL
ACUÑA CASTRO RONALD MAURICIO
ADAM FRIDOVICH EDWARD
AGUILAR GONZÁLEZ JOSÉ MANUEL
ÁLEMAN BRICEÑO JORGE EDUARDO


Can someone tell me how to do that?

Thanks in advance!
 
D

Dave Peterson

You can use a formula like:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(A1<>"")
or if there could be multiple spaces where only one should be:
=LEN(trim(A1))-LEN(SUBSTITUTE(trim(A1)," ",""))+(trim(A1)<>"")
 
S

sybmathics

TxWebdesigner confabulated:
Hi,

I have a long list of full names, Spanish names at that, that need to be
separated into First Name and Last Name columns.
Click to expand...
etc....

Why don't you use Data --> Text to columns

Run the wizard, then you'll have spaces removed.

With the IF function you can then decidewaht to do with empty or
obsolete columns.

HTH,

Sybolt
 
Top