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]

 

[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