Separate numbers to the columns

[shimaa01234]

I want to separate the following numbers, and columns to respond but the latter figure becomes the first number like this:

84	4	8	0	0
118	8	1	1	0
51	1	5	0	0
93	3	9	0	0
160	0	6	1	0
170	0	7	1	0
182	2	8	1	0
191	1	9	1	0

<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>
</tbody>

 

[Chris Mack]

What result do you want?

 

[Special-K99]

Assuming numbers are in column A and NONE of them are longer 4 digits in length

in B1
=MID(RIGHT("000"&$A1,4),4-(COLUMN()-2),1)
copy across to column E
then copy down the column

@CHRIS: Looks like he's after the first column reversed with leading digits to bring the number to 4 characters in length

 

[FormR]

Hi, here is one way - the formula in B2 is copied down and across.

Excel Workbook
	A	B	C	D	E
2	84	4	8	0	0
3	118	8	1	1	0
4	51	1	5	0	0
5	93	3	9	0	0
6	160	0	6	1	0
7	170	0	7	1	0
8	182	2	8	1	0
9	191	1	9	1	0
Sheet1

 

[ParamRay]

.
.

If you want a macro to achieve this, try the following code:

Sub SplitNumbers()

    Dim WRng As Range
    Dim Cell As Range
    Dim Lgth As Byte
    Dim i As Byte
    
    'Exit if worksheet not active
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    
    'Set range to work with
    With ActiveSheet
        Set WRng = Intersect(.Columns("A"), .UsedRange)
    End With
    
    'Loop through cells and
    'split the characters...
    
    For Each Cell In WRng
        Lgth = Len(Cell.Value)
        If Lgth > 0 Then
            For i = 1 To Lgth
                Cell.Offset(0, i).Value = Mid(Cell.Value, i, 1)
            Next i
        End If
    Next Cell
        
End Sub

 

[a massive zebra]

If your numbers start in cell A1, enter this into cell B1 and copy across and down: =MID(RIGHT("000"&$A1,4),4-(COLUMN()-2),1)

 

[shimaa01234]

Wonderful
But if the number like this, "123456789034321"

 

[FormR]

Wonderful
But if the number like this, "123456789034321"

The formula in post #4 will work regardless of the length of the number. Copy across as far as needed.

 

[shimaa01234]

Solutions fantastic
Thank you very much Mr. "FormR" on this wonderful formula
Thank you Mister, "gpeacock" this wonderful "code"
Thank you very much Mr. "special-99" on this formula but not sperate more than 4 numbers