Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Board Regular
    Join Date
    Jun 2011
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,032
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default 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. #3
    New Member Bo_Ry's Avatar
    Join Date
    Oct 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,216
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Number formatting into Sets of 3

    Quote Originally Posted by Bo_Ry View Post
    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member Bo_Ry's Avatar
    Join Date
    Oct 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Number formatting into Sets of 3

    Quote Originally Posted by Rick Rothstein View Post
    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. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Number formatting into Sets of 3

    Edit: Ooops, hadn't seen post 5

    Quote Originally Posted by Rick Rothstein View Post
    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 by Peter_SSs; Sep 21st, 2019 at 12:00 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,216
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Number formatting into Sets of 3

    Quote Originally Posted by Bo_Ry View Post
    600 need to be on the left
    Yikes! I completely missed that!!!
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,969
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default 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

    ABCD
    213065102013065102
    313165013165
    44051065004051065
    5600600
    618013092018013092

    Spreadsheet Formulas
    CellFormula
    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)

    ABCDEF
    256989513065102056989513065102
    33003
    44051065004051065
    5600000000000600000000000
    618013092018013092

    Spreadsheet Formulas
    CellFormula
    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
    Last edited by Peter_SSs; Sep 21st, 2019 at 12:53 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    240
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by mohadin; Sep 21st, 2019 at 03:13 AM.

  10. #10
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    240
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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