Thread: Number formatting into Sets of 3 Thanks: 0 Likes: 0

1. Number formatting into Sets of 3

I have numbers in column A that look like this
 13,065,102 13,165 4,051,065 600 18,013,092

But when I click in the formula bar they are really this (real numbers)
 13065102 13165 4051065 600 18013092

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

2. Re: Number formatting into Sets of 3

Using vba:

Code:
```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
ABCD
1
213,065,102013065102
313,165013165
44,051,065004051065
5600600
618,013,092018013092

Sheet2

3. Re: Number formatting into Sets of 3

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

4. Re: Number formatting into Sets of 3

Originally Posted by 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)
A little simpler formula...

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

5. Re: Number formatting into Sets of 3

Originally Posted by Rick Rothstein
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"

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

6. Re: Number formatting into Sets of 3

Edit: Ooops, hadn't seen post 5

Originally Posted by Rick Rothstein
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.

7. Re: Number formatting into Sets of 3

Originally Posted by Bo_Ry
600 need to be on the left
Yikes! I completely missed that!!!

8. Re: Number formatting into Sets of 3

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

Sets of 3

 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

 Cell Formula B2 =TEXT(LEFT(A2,MOD(LEN(A2)-1,3)+1),"000") C2 =LEFT(SUBSTITUTE(\$A2,\$B2+0,"",1),3) D2 =SUBSTITUTE(\$A2,(B2&C2)+0,"",1)

Excel tables to the web >> Excel Jeanie HTML 4

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

Sets of 3 (2)

 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

 Cell Formula B2 =TEXT(LEFT(A2,MOD(LEN(A2)-1,3)+1),"000") C2 =LEFT(SUBSTITUTE(\$A2,CONCAT(\$B2:B2)+0,"",1),3)

Excel tables to the web >> Excel Jeanie HTML 4

9. Re: Number formatting into Sets of 3

Hi,
I would suggest
Code:
```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```

10. Re: Number formatting into Sets of 3

Small modi
Just add the colored line
Code:
```a = Application.Transpose(Range("b2").Resize(lr))
Range("c2:e" & lr).Resize(lr).NumberFormat = "000"
Application.ScreenUpdating = False```

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•