Separating string of numbers from one cell into individual cell.

[serge]

Hi,
I have a macro that return in cell AL8, 5 numbers of 2 digits, I wanted to know if it would be possible to separate each number from that one cell into individual cell ?
If yes what formula could do that ?

 

[Kerryx]

Use excel Text to Columns option think it was available in 2007 version

Book1
	A	B	C	D	E	F	G
1	09 16 41 46 59	9	16	41	46	59
Sheet1

Book1
	A
1	9
Sheet1

 

[serge]

Hello, Kerryx, Thank you for your response that's a good idea but, I need to keep the original data intact.

 

[Kerryx]

Ok set destination to $B$1in fianl step will keep original datat in column A

 

[serge]

I looked everywhere, can't find it ?
Do you think a formula could do the job ?

 

[Kerryx]

Excel 2007

 

[Kerryx]

Yes a formula could do the job but more complicated do all the cells have 5 seperate numbers in the cell or do some have more or less numbers in the cells in column A, would you have examples to work with?
Use the XL2BB addin to provide examples of what yu need split

 

[Kerryx]

Formula option

Book1
	A	B	C	D	E	F
2	09 16 41 46 59	09	16	41	46	59
Sheet1

Cell Formulas
Range	Formula
B2	B2	=LEFT(A2, SEARCH(" ",A2,1)-1)
C2	C2	=MID($A$2, SEARCH(" ",$A$2) + 1, SEARCH(" ",$A$2,SEARCH(" ",$A$2)+1) - SEARCH(" ",$A$2) - 1)
D2	D2	=MID($A$2, SEARCH(" ",$A$2) + 4, SEARCH(" ",$A$2,SEARCH(" ",$A$2)+4) - SEARCH(" ",$A$2) - 4)
E2	E2	=MID($A$2, SEARCH(" ",$A$2) + 7, SEARCH(" ",$A$2,SEARCH(" ",$A$2)+7) - SEARCH(" ",$A$2) - 6)
F2	F2	=RIGHT(A2, SEARCH(" ",A2,1)-1)

 

[serge]

Thank you so much Kerryx, you solve my problem, really appreciate your help, thank you again.