# Separate numbers to the columns

#### shimaa01234

##### Active Member
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>

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
What result do you want?

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

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

Excel Workbook
ABCDE
2844800
31188110
4511500
5933900
61600610
71700710
81822810
91911910
Sheet1

.
.

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

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

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)

Wonderful
But if the number like this, "123456789034321"

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.

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

Replies
3
Views
136
Replies
5
Views
252
Replies
3
Views
256
Replies
1
Views
495
Replies
4
Views
116

1,221,493
Messages
6,160,138
Members
451,623
Latest member
Christine4711

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back