Adding a "Comma" Punctuation in between Cell Data

[Neavalmi]

Hi Excel Experts:

I want to send email to all the names of the team members of a particular db in lotus notes for a particular memo. I was able to export all these names in excel file like below.

Name
Janet Wong
Jane Yu
Johnny Tan
Jacky Shin

The names are around 3,000 plus and i would like to paste these in the bcc field of LN email. However, the names need to be separated by comma. How can i do this?

My idea to first transfer all names with comma in a single cell and then copy and paste this into the bcc field. How can i add the comma in this case?

Other than what i know, do you have any other excellent method to do this?

Thank you in advance for your help.

Nea

 

[mikerickson]

Trivially, you could use

=ConcatIf(A1:A10, "pi", B1:B10, ",") & ConcatIf(A1:A10, "odl", B1:B10, ",")



Or you could use the UDF, ConcatOR

=ConcatOR(A1:A10,B1:B10, "pi", B1:B10, "odl", ",")

Function ConcatOR(StringsRange As Range, ParamArray Criterias() As Variant) As String
    Dim Delimiter As String
    Dim Size As Long
    Dim RowCount As Long, ColumnCount As Long
    Dim RowOfInterest As Long, ColumnOfInterest As Long
    Dim i As Long, j As Long, k As Long
    Dim flag As Boolean
    
    Delimiter = " ": Rem default delimiter
    Size = UBound(Criterias)
    If UBound(Criterias) Mod 2 = 0 Then
        Delimiter = Criterias(UBound(Criterias))
        Size = Size - 1
    End If
    
    With StringsRange
        Set StringsRange = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    RowCount = StringsRange.Rows.Count
    ColumnCount = StringsRange.Columns.Count
    For k = 0 To Size Step 2
        With Criterias(k)
            Set Criterias(k) = Application.Intersect(.Cells, .Parent.UsedRange)
        End With
        
        With Criterias(k)
            If RowCount < .Rows.Count Then RowCount = .Rows.Count
            If ColumnCount < .Columns.Count Then ColumnCount = .Columns.Count
        End With
    Next k
    
    For i = 1 To RowCount
        For j = 1 To ColumnCount
            flag = False
            For k = 0 To Size Step 2
                RowOfInterest = Application.Min(i, Criterias(k).Rows.Count)
                ColumnOfInterest = Application.Min(j, Criterias(k).Columns.Count)
                flag = flag Or (WorksheetFunction.CountIf(Criterias(k).Cells(RowOfInterest, ColumnOfInterest), Criterias(k + 1)) = 1)
            Next k
            If flag Then
                RowOfInterest = Application.Min(i, StringsRange.Rows.Count)
                ColumnOfInterest = Application.Min(j, StringsRange.Columns.Count)
                ConcatOR = ConcatOR & Delimiter & CStr(StringsRange.Cells(RowOfInterest, ColumnOfInterest).Value)
            End If
        Next j
    Next i
    ConcatOR = Mid(ConcatOR, Len(Delimiter) + 1)
End Function

 

[Tet Htut Naing]

Dear All,
I made a mistake yesterday. In replace of Concat() formula, I just want to mean Mr. Mikerickson's VBA code, and his formula Concatif().

I Appologize for happening this mistake.

I have an enquiry to this formula. Is it possible for "two variables" that can be input into a cell for Concatif(), as I asked yesterday.

Thanks

 

[mikerickson]

Is it possible for "two variables" that can be input into a cell for Concatif()?

What happened when you tried it?
What formula did you use when you tried it?

 

[Tet Htut Naing]

Oh My God,
You, Mr. Mikerickson, reply instantly. How Great. Thanks. Thanks a lot.
I didnot see your reply at first. That's why I wrote above "reply".
OK, I see your response and answer now. I will work on it and will be back soon.

If I bother you, sorry for this. I have two other problems I have to tackle. My organization is NGO, and HR department calls for help from me to write an excel that control all the staff information.
1. Office attendence, (arrival time) and Leave Records
2. Personal Information
3. Project Related Information for each staff.
4. Education and skills.
I would create later a new thread for my each questions.

Thanks Mr. Mikerickson
Htut

 

[Tet Htut Naing]

Hi Mr. Mikerickson,
I changed a bit your formula Concatif.
=Concatif(E4:M4, "pl", E3:M3, ",") &","& Concatif(E4:M4, "odl", E3:M3, ",")
so that it would put a comma between pl list and odl list. It is successful.
Thank you.
Your Sincerely,
Htut

 

[Tet Htut Naing]

Hi, Mr Mikerickson,
Sorry to let you know, that ConcatOR is not working.
But Thanks
Your Sincerely,
Htut