Adding a comma between single digits in a cell
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Adding a comma between single digits in a cell

  1. #1
    New Member
    Join Date
    Jun 2019
    Location
    Europe
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Adding a comma between single digits in a cell

    I am looking for a way to add a comma after every single digit in a cell.
    I found some formulas on the web but since the number of digits is not equal in the cells, I can't get the result I need.

    For example, in column A:
    12345
    12
    135789
    135
    etc.

    What formula can give me the result I need? can I get some help, please? thanks.
    Expected results of the above example:
    1,2,3,4,5
    1,2
    1,3,5,7,8,9
    1,3,5

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    Try:

    Code:
    =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,",0"),1,",1"),2,",2"),3,",3"),4,",4"),5,",5"),6,",6"),7,",7"),8,",8"),9,",9"),2,999)
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Jun 2019
    Location
    Europe
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    Thanks, Eric. That was quick, and that's hell of a formula

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,162
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    This macro will place the results in column B.
    Code:
    Sub lind33()
        Dim i As Long, rng As Range, RngList As Object
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set RngList = CreateObject("Scripting.Dictionary")
        For Each rng In Sheets("Sheet1").Range("A1:A" & LastRow)
            For i = 1 To Len(rng)
                RngList.Add Mid(rng, i, 1), Nothing
            Next i
            rng.Offset(0, 1) = Join(RngList.keys, ", ")
            RngList.RemoveAll
        Next rng
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    Yeah, it is quite a formula, isn't it?!

    If you have Excel 365 with the TEXTJOIN function, you can simplify it to:

    =TEXTJOIN(",",1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

    confirmed with Control+Shift+Enter. But I didn't want to assume you had that. In any event, glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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

    Default Re: Adding a comma between single digits in a cell

    Quote Originally Posted by mumps View Post
    This macro will place the results in column B.
    Code:
    Sub lind33()
        Dim i As Long, rng As Range, RngList As Object
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Set RngList = CreateObject("Scripting.Dictionary")
        For Each rng In Sheets("Sheet1").Range("A1:A" & LastRow)
            For i = 1 To Len(rng)
                RngList.Add Mid(rng, i, 1), Nothing
            Next i
            rng.Offset(0, 1) = Join(RngList.keys, ", ")
            RngList.RemoveAll
        Next rng
    End Sub
    Your macro can be written using only one loop...
    Code:
    Sub AddCommasBetweenDigits()
      Dim X As Long, LastRow As Long, Cell As Range
      LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      For Each Cell In Sheets("Sheet2").Range("A1:A" & LastRow)
        Cell.Offset(, 1) = Replace(StrConv(Cell.Value, vbUnicode), Chr(0), ",", , Len(Cell.Value) - 1)
      Next
    End Sub
    HOW TO INSTALL MACROs
    ------------------------------------
    If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (AddCommasBetweenDigits) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
    Last edited by Rick Rothstein; Jul 21st, 2019 at 01:52 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,668
    Post Thanks / Like
    Mentioned
    64 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    Try whit UDF (User Definition Function)

    Code:
    Function AddComma(s As Range)
        Dim i As Long, cad As String
        For i = 1 To Len(s)
            cad = cad & Mid(s, i, 1) & ","
        Next
        AddComma = Left(cad, Len(cad) - 1)
    End Function
    Use like formula excel:

    =AddComma(A1)
    Regards Dante Amor

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

    Default Re: Adding a comma between single digits in a cell

    Quote Originally Posted by Eric W View Post
    Yeah, it is quite a formula, isn't it?!

    If you have Excel 365 with the TEXTJOIN function, you can simplify it to:

    =TEXTJOIN(",",1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

    confirmed with Control+Shift+Enter. But I didn't want to assume you had that. In any event, glad we could help!
    Actually Eric, this can be done with a "simple" normally-entered formula in any version of Excel (that I am familiar with)...

    =MID(TEXT(A1,REPT("\,0",LEN(A1))),2,2*LEN(A1))
    Last edited by Rick Rothstein; Jul 21st, 2019 at 02:01 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Jun 2019
    Location
    Europe
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding a comma between single digits in a cell

    Thank you, guys all, for your great answers really. I appreciate it.

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

    Default Re: Adding a comma between single digits in a cell

    Quote Originally Posted by DanteAmor View Post
    Try whit UDF (User Definition Function)

    Code:
    Function AddComma(s As Range)
        Dim i As Long, cad As String
        For i = 1 To Len(s)
            cad = cad & Mid(s, i, 1) & ","
        Next
        AddComma = Left(cad, Len(cad) - 1)
    End Function
    Use like formula excel:

    =AddComma(A1)
    Given the formula I posted in Message #8 , a UDF for this functionality (if one was thought to be necessary now) could be written as a non-looping one-liner...
    Code:
    Function AddCommas(s As String) As String
      AddCommas = Evaluate(Replace("MID(TEXT(@,REPT(""\,0"",LEN(@))),2,2*LEN(@))", "@", s))
    End Function
    Last edited by Rick Rothstein; Jul 21st, 2019 at 02:32 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •