Number formatting into Sets of 3

[anthonyexcel]

I have numbers in column A that look like this

13,065,102

13,165

4,051,065

600

18,013,092

<tbody>
</tbody>

But when I click in the formula bar they are really this (real numbers)

13065102

13165

4051065

600

18013092

<tbody>
</tbody>

What I need to do is to is put the numbers into sets of 3 from right to left

13065102	013	065	102
13165	013	165
4051065	004	051	065
600	600
18013092	018	013	092

<tbody>
</tbody>

 

[mohadin]

Hi again
This new code is for general farmat( 123456 or 12,345,3....)

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{1,3}"
        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) = Format(Sres(j), "000")
                x = x + 1
            Next
            Range("c" & i + 1).Resize(, UBound(b)) = b
            x = 0
        Next
    End With
    Application.ScreenUpdating = True
End Sub

 

[mohadin]

Repeated so

 

[anthonyexcel]

Wow!! Thanks to all who contributed to this thread! I appreciate all of the help and guidance!! Thanks you all again!

 

[mohadin]

You are well come
Be happy

 

[Peter_SSs]

For a macro approach, my suggestion (assuming max of 9 digits at this stage) would not involve any looping through rows.

Sub SetsOf3()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("1&right(rept(0,9)&#,ceiling(len(#),3))", "#", .Offset(, -1).Address))
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 2), Array(4, 2), Array(7, 2))
  End With
End Sub