Number formatting into Sets of 3

[anthonyexcel]

I have numbers in column A that look like this
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]13,065,102[/TD]
[/TR]
[TR]
[TD]13,165[/TD]
[/TR]
[TR]
[TD]4,051,065[/TD]
[/TR]
[TR]
[TD]600[/TD]
[/TR]
[TR]
[TD]18,013,092[/TD]
[/TR]
</tbody>[/TABLE]



But when I click in the formula bar they are really this (real numbers)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]13065102[/TD]
[/TR]
[TR]
[TD]13165[/TD]
[/TR]
[TR]
[TD]4051065[/TD]
[/TR]
[TR]
[TD]600[/TD]
[/TR]
[TR]
[TD]18013092[/TD]
[/TR]
</tbody>[/TABLE]


What I need to do is to is put the numbers into sets of 3 from right to left
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]13065102[/TD]
[TD]013[/TD]
[TD]065[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]13165[/TD]
[TD]013[/TD]
[TD]165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4051065[/TD]
[TD]004[/TD]
[TD]051[/TD]
[TD]065[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18013092[/TD]
[TD]018[/TD]
[TD]013[/TD]
[TD]092[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Using vba:

Sub a1110422a()
Dim c As Range, j As Long
Columns("B:D").NumberFormat = "@"
    For Each c In Range("A2", Cells(Rows.count, "A").End(xlUp))
        j = 0
        For Each x In Split(c.text, ",")
        j = j + 1: c.Offset(, j) = Format(x, "000")
        Next
    Next
End Sub

Excel 2013/2016
	A	B	C	D
1
2	13,065,102	013	065	102
3	13,165	013	165
4	4,051,065	004	051	065
5	600	600
6	18,013,092	018	013	092
Sheet1

<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

 

[Bo_Ry]

Number in A2
Please try at B2:D2
=MID(TEXT($A2,REPT(0,CEILING(LEN($A2),3))),COLUMNS($B2:B2)*3-2,3)

 

[Rick Rothstein]

Number in A2
Please try at B2:D2
=MID(TEXT($A2,REPT(0,CEILING(LEN($A2),3))),COLUMNS($B2:B2)*3-2,3)

A little simpler formula...

=MID(TEXT($A2,"000000000"),3*COLUMNS($B:B)-2,3)

 

[Bo_Ry]

A little simpler formula...

=MID(TEXT($A2,"000000000"),3*COLUMNS($B:B)-2,3)

600 need to be on the left, that where I use REPT(0,CEILING(LEN($A2),3)) instead of "000000000"



[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]13065102[/TD]
[TD]013[/TD]
[TD]065[/TD]
[TD]102[/TD]
[/TR]
[TR]
[TD]13165[/TD]
[TD]013[/TD]
[TD]165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4051065[/TD]
[TD]004[/TD]
[TD]051[/TD]
[TD]065[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18013092[/TD]
[TD]018[/TD]
[TD]013[/TD]
[TD]092[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Edit: Ooops, hadn't seen post 5

A little simpler formula...

=MID(TEXT($A2,"000000000"),3*COLUMNS($B:B)-2,3)

Problem is it doesn't do what Bo-Ry's does or what the OP requested.

 

[Rick Rothstein]

600 need to be on the left

Yikes! I completely missed that!!!

 

[Peter_SSs]

Another formula option is to use different formula for the 3 columns, keeping the formulas much simpler.
Each formula copied down.

Excel Workbook
	A	B	C	D
2	13065102	013	065	102
3	13165	013	165
4	4051065	004	051	065
5	600	600
6	18013092	018	013	092
Sets of 3

If the numbers can be longer than 9 digits & you have the CONCAT function then ..
B2 copied down
C2 copied across and down

Excel Workbook
	A	B	C	D	E	F
2	56989513065102	056	989	513	065	102
3	3	003
4	4051065	004	051	065
5	600000000000	600	000	000	000
6	18013092	018	013	092
Sets of 3 (2)

 

[mohadin]

Hi,
I would suggest

Sub test()
    Dim a, b As Variant
    lr = Cells(Rows.Count, 2).End(xlUp).Row - 1
    a = Application.Transpose(Range("b2").Resize(lr))
    Application.ScreenUpdating = False
    ReDim b(1 To 1)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+"
        For i = 1 To lr
            Set Sres = .Execute(a(i))
            ReDim b(1 To Sres.Count)
            For j = 0 To Sres.Count - 1
                b(j + 1) = Formt(Sres(j),"000")
            Next
            Range("c" & i + 1).Resize(, UBound(b)) = b
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[mohadin]

Small modi
Just add the colored line

a = Application.Transpose(Range("b2").Resize(lr))
[COLOR=#ff0000]    Range("c2:e" & lr).Resize(lr).NumberFormat = "000"[/COLOR]
    Application.ScreenUpdating = False