I have under column A numbers with different lengths.
A1=125
A2=1025
A3=9
A4=589644
ETC....

I want to separate each individual number into a cell.
for example
A1=1
B1=2
C1=5
A2=1
B2=0
C2=2
D2=5
ETC...

I've tried TEXT TO COLUMNS, but I have to use fixed width, and sometimes
it's just to long, is there an easier way??

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Split_Em()
Dim MyRange As Range, x as long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
For x = 1 To Len(c)
Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, 2) = Mid(c, x, 1)
Next
Next
Columns("A").EntireColumn.Delete
End Sub


Mike

"GEM" wrote:

I have under column A numbers with different lengths.
A1=125
A2=1025
A3=9
A4=589644
ETC....

I want to separate each individual number into a cell.
for example
A1=1
B1=2
C1=5
A2=1
B2=0
C2=2
D2=5
ETC...

I've tried TEXT TO COLUMNS, but I have to use fixed width, and sometimes
it's just to long, is there an easier way??

Use

=IF(LEN($A1)=COLUMN(A1),--MID($A1,COLUMN(A1),1),"")

and copy over as many columns as you think you will need.

--
__________________________________
HTH

Bob

"GEM" wrote in message
...
I have under column A numbers with different lengths.
A1=125
A2=1025
A3=9
A4=589644
ETC....

I want to separate each individual number into a cell.
for example
A1=1
B1=2
C1=5
A2=1
B2=0
C2=2
D2=5
ETC...

I've tried TEXT TO COLUMNS, but I have to use fixed width, and sometimes
it's just to long, is there an easier way??

misread you post, forget this

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub Split_Em()
Dim MyRange As Range, x as long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
For x = 1 To Len(c)
Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, 2) = Mid(c, x, 1)
Next
Next
Columns("A").EntireColumn.Delete
End Sub


Mike

"GEM" wrote:

I have under column A numbers with different lengths.
A1=125
A2=1025
A3=9
A4=589644
ETC....

I want to separate each individual number into a cell.
for example
A1=1
B1=2
C1=5
A2=1
B2=0
C2=2
D2=5
ETC...

I've tried TEXT TO COLUMNS, but I have to use fixed width, and sometimes
it's just to long, is there an easier way??