Adding a "Comma" Punctuation in between Cell Data

Putting 3000+ names into one cell sound ambitious, however, if this is a one off exercise and you don't want to use macros, then.. lets say your names are in column A, starting at row 2.
In B2 enter this formula:
=A2

In B3 enter this formula:
=B2 & "," & A3

copy B3 downwards. The bottommost cell will contain the sort of thing you're looking for, but be aware that it might not contain all the names since (in xl 2003) there is a limit of some 32000 characters in one cell.

3000 is a heroic number of emails to put in one cell (it probably interacts with the 255 character limit).

But ConcatIf might help

=ConcatIf(A:A,"<>",A:A,",") will return all the values in column A, with a comma delimiter

p45cal said:

Putting 3000+ names into one cell sound ambitious, however, if this is a one off exercise and you don't want to use macros, then.. lets say your names are in column A, starting at row 2.
In B2 enter this formula:
=A2

In B3 enter this formula:
=B2 & "," & A3

copy B3 downwards. The bottommost cell will contain the sort of thing you're looking for, but be aware that it might not contain all the names since (in xl 2003) there is a limit of some 32000 characters in one cell.

Click to expand...

Hi p45cal,
Thanks. It worked. However, it didn't come to my mind that it is very inconvenient when the cell is being highlighted, my whole screen is flashing with the the names. I can't even select all the contents in the cell as i have to manually scroll the mouse.

I would like to try the macro version if you be so kind enough to share.
Thanks.
Nea

mikerickson said:

3000 is a heroic number of emails to put in one cell (it probably interacts with the 255 character limit).

But ConcatIf might help

=ConcatIf(A:A,"<>",A:A,",") will return all the values in column A, with a comma delimiter

Code:

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                            Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                stringsRange.Column - compareRange.Column)
 
        For i = 1 To compareRange.Rows.Count
            For j = 1 To compareRange.Columns.Count
                If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                    If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                        ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                    End If
                End If
            Next j
        Next i
        ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
    End Function

Click to expand...

Hi Mike ,
Can't seem to figure how to make this work. Can you pls explain more, i'm not much an expert in macros, i just copy and run the macro. But nothing seems to happen.

I haven't written a macro.. yet. However, you don't need to select all the contents of the cell; sure, use the mouse to navigate to the cell, then on the keyboard use Ctrl + C to copy the contents, and Ctrl + v to paste them in the Bcc field.

Neavalmi said:

Hi p45cal,
Thanks. It worked. However, it didn't come to my mind that it is very inconvenient when the cell is being highlighted, my whole screen is flashing with the the names. I can't even select all the contents in the cell as i have to manually scroll the mouse.

I would like to try the macro version if you be so kind enough to share.
Thanks.
Nea

Click to expand...

Open the VBEditor and paste that code into a normal module.
The UDF (User Defined Function) it encodes is called ConcatIf which has similar arguments to SumIf.

I am working on a similar problem and through search of website, found this post. I have tried using the function as posted as well as trying a couple of modifications and continue to get a #VALUE! error. My data is simply a list of account numbers (alphanumeric) in column A that I need to separate with a comma.

Any help would be greatly appreciated.

More simple than I thought.

That last line eliminates the comma that precedes the first data element in the string.

Thanks to everyone for getting me going in the right direction...

Hi,
It really worked for me, thanks.
But I changed a bit in the code you mentioned above.
ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
This line is replace with
ConcatIf = ConcatIf & CStr(stringsRange.Cells(i, j)) & ","
Then
The comma punctuation follows only after the CStr.

Could you please explain me a bit more about my case.
I have two interchangeable letters I want to concatenate.

Column A Column B

This is how I come up with your amazing formula, Concat (=Concat(A1:A4,A6,B1:B4))
pl = Personal Late
This is for only one condition. What I want to add is that if pl or odl ( On Duty Late) is entered, then do the same job.
Is it Possible?
Would you mind to help a bit more. I am searching this solution for ages, That's why.
However, thanks you very much.
Htut