• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Want to separate no. string with "," comma

S

Samadhan Gaikwad

Member
Hi,

I have numbers in only one cell in multiple of 7 e.g. 691611544656555465554

I wanted it to display: 6916115,4465655,5465554 using number formating.

Thank you!
 
Thank you both of you but I think formula does not work when string length varies. Say 70 or even 700.
Actually my requirement is user will enter/list 7 digit numbers in one column and want them separated with comma in a cell.
 
You will need Office 365 and Excel 2016 for this:
=TEXTJOIN(",",,MID(A1,(ROW(OFFSET($A$1,,,LEN(A1)/8,1))-1)*8+1,8)) Ctrl+Shift+Enter

upload_2017-2-8_15-36-59.png

See attached file:
 

Attachments

  • Book1.xlsx
    9.4 KB · Views: 2
Thank you Hui,
But I have Excel 2010 and so it is not working.
Can we accomplish the task through VBA: Column "A'' contains 7 digit numbers (cell range may vary), concatenate all filled cells from A and add comma in between them and put them in B1?
I had recorded vba manually, it was working fine but now due to some issue it is not working.
 
If string length varies, this formula will work for less than 21 digit number.

=REPLACE(REPLACE(A1,MAX(LEN(A1)-6,1),,IF(LEN(A1)>7,",","")),MAX(LEN(A1)-13,1),,IF(LEN(A1)>14,",",""))

Regards
Bosco
 
Thank you Bosco.
Yeah, string length will always be varying in multiples of 7. But as I said earlier it may go to 700 to 7000. So I think only VBA or number formatting will work in my case.
 
Here is some VBA that will do what you want

Code: 
Public Function Breakout_Text(myStr As String, _
  Optional myDelim As String = ",", _
  Optional myLen As Integer = 8) As String

Dim sl As Integer
Dim tmp As String

sl = Len(myStr)
For i = 1 To sl Step myLen
  If sl - i < myLen Then
  tmp = tmp + myDelim + Right(myStr, sl - i + 1)
  Else
  tmp = tmp + myDelim + Mid(myStr, i, myLen)
  End If
Next i

Breakout_Text = Right(tmp, Len(tmp) - 1)

End Function

To use it copy and paste this into a Code Module in VBA

To use:
=Breakout_Text(Cell, [Delimiter], [Length])
Optional Delimiter default = ,
Optional Length default = 8

=Breakout_Text(A1)
sample result: 12345678,90123456,789012

=Breakout_Text(A1,"-")

sample result: 12345678-90123456-789012

=Breakout_Text(A1,";", 5)
sample result: 12345;67890;12345;67890;12

see sample file attached

[Edit by Hui, Whoops]
 

Attachments

  • Breakout Text.xlsm
    16.8 KB · Views: 4
Last edited:
Hi to all!

Another VBA option could be:
Code: 
Function SplitDelimText$(C As Range, Optional Del$ = ",", Optional n& = 8)
    Dim a:  ReDim a(1 To 1)
   
    For i = 1 To Application.RoundUp(Len(C) / n, 0)
        ReDim Preserve a(1 To i)
        a(i) = Mid(C, 1 + n * (i - 1), n)
    Next i
   
    SplitDelimText = Join(a, Del): Erase a
End Function

I use Hui's file to put the answers.
@Hui: Check the results in last position of String.

Blessings!
 

Attachments

  • Breakout Text.xlsm
    16.4 KB · Views: 2
Code edited in above post

Revised file attached, Includes Johns code as well:
 

Attachments

  • Breakout Text_2.xlsm
    16.4 KB · Views: 1
Code: 
Sub Comma()
'
' Macro3 Macro
'

'
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C&"",""&RC[-1]"
    Range("I3").Select
    Selection.AutoFill Destination:=Range("I3:I1439"), Type:=xlFillDefault
    Range("I3:I1439").Select
    Range("I1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    Selection.Copy
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=",,", Replacement:=",", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("J1").Select
    Columns("J:J").ColumnWidth = 66.56
End Sub


Above is the code which does what I want. Recorded the macro manually. I have a text or numbers in "H" column. Did some formula stuff in "I" column (intentionally I kept it hidden while running macro), and put all comma separated strings in "J1" cell. And finally replaced ",," with ",". And I am done. It can work for any no. of cells. In this case it is 1439.
 
You must log in or register to reply here.
Back
Top