Number formatting into Sets of 3

anthonyexcel

Board Regular
Joined
Jun 10, 2011
Messages
218
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
13065102013065102
13165013165
4051065004051065
600600
18013092018013092

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,568
Office Version
365
Platform
Windows
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
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">13,065,102</td><td style="text-align: right;;">013</td><td style="text-align: right;;">065</td><td style="text-align: right;;">102</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">13,165</td><td style="text-align: right;;">013</td><td style="text-align: right;;">165</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">4,051,065</td><td style="text-align: right;;">004</td><td style="text-align: right;;">051</td><td style="text-align: right;;">065</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">600</td><td style="text-align: right;;">600</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">18,013,092</td><td style="text-align: right;;">018</td><td style="text-align: right;;">013</td><td style="text-align: right;;">092</td></tr></tbody></table><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

Board Regular
Joined
Oct 27, 2018
Messages
67
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,804
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Oct 27, 2018
Messages
67
Office Version
365
Platform
Windows
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"



13065102013065102
13165013165
4051065004051065
600600
18013092018013092

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
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. ;)
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
Another formula option is to use different formula for the 3 columns, keeping the formulas much simpler.
Each formula copied down.

Excel Workbook
ABCD
213065102013065102
313165013165
44051065004051065
5600600
618013092018013092
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
ABCDEF
256989513065102056989513065102
33003
44051065004051065
5600000000000600000000000
618013092018013092
Sets of 3 (2)
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Small modi
Just add the colored line
Code:
a = Application.Transpose(Range("b2").Resize(lr))
[COLOR=#ff0000]    Range("c2:e" & lr).Resize(lr).NumberFormat = "000"[/COLOR]
    Application.ScreenUpdating = False
 

Watch MrExcel Video

Forum statistics

Threads
1,095,234
Messages
5,443,273
Members
405,222
Latest member
Summer01

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top