Splitting Numbers into Columns.
 

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??

Splitting Numbers into Columns.
 

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??

Splitting Numbers into Columns.
 

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??

Splitting Numbers into Columns.
 

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??