Split Multiple Names in One Cell

[2blues]

Hi All,

I have data that has multiple names all in one cell, which looks like this:

A John Smith B Dave Smith C Jack Jones D Eric Jones

Is there anyway I can split the names into individual cells? It is ok if the letter comes with the name like so:

A John Smith
B Dave Smith
C Jack Jones
D Eric Jones

They can split either horizontally or vertically.

Thank you

 

[PaddyD]

Hi - welcome to the board.

The easiest way is to use text to columns: menu path is - data | text to column, then follow the prompts

If you want a formula:

=left(a1,find(" ",a1)-1)

=right(a1,len(A1)-find(" ",a1))

 

[jtakw]

Hi,

Assuming the names in your data string is Always 2 words (as in your sample), separated by a Single character:

Excel 2010
	A	B	C	D	E	F
1	A John Smith B Dave Smith C Jack Jones D Eric Jones E John Doe	A John Smith	B Dave Smith	C Jack Jones	D Eric Jones	E John Doe
2	A John Smith B Dave Smith C Jack Jones D Eric Jones E John Doe	John Smith	Dave Smith	Jack Jones	Eric Jones	John Doe
Sheet11

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),COLUMNS($A1:A1)*300-299,300))
B2	=MID(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMNS($A2:A2)*300-299,300)),3,255)

Use B1 formula copied across if you want to keep the Single character preceding the Name (i.e. A, B, C, D, etc.)
Use B2 formula copied across if don't want to keep the Single character preceding the Name.

Formulas can be copied as far across as necessary for longer strings of the same structure (as many names as you have separated by a single character)

 

[PaddyD]

Given that jtakw managed to read and answer the question you asked, rather than some other question as I did, I'd go with that answer

 

[2blues]

Given that jtakw managed to read and answer the question you asked, rather than some other question as I did, I'd go with that answer

Haha thanks everyone, formulas worked great.

 

[jtakw]

You're welcome, welcome to the forum.