Splitting Numbers into Columns.

  • Thread starter Thread starter GEM
  • Start date Start date
G

GEM

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
 
Use

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

and copy over as many columns as you think you will need.
 
misread you post, forget this

Mike H said:
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
Click to expand...
 
Back
Top