Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Concate email address

    Hello,

    I have a database of our clients wherein from column C to column F i have email address now i want to concate them with a semicolon [;] and to have them in column G. At times i have email address in 2 column or 3 column also and 2 columns or 1 column is blank.
    Thanks in advanve.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Zmagic,
    I hope this is what you are looking to do. Both codes will concatenate the emails separated by a semicolon into Column G. The first code will concatenate on one line while the second code will format the emails on multiple lines within the cell.

    Note: If you want to concatenate on one line and would like a space after the semicolon then see comment on line

    HTH,
    Maud

    Single Line:
    ConcatEmails1.png

    Multi-Line:
    ConcatEmails2.png

    Code:
    Public Sub ConcateEmails()
    'CONCATENATE ONE LINE CELL
    LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    For I = 1 To LastRow
        For J = 3 To 6
            If Cells(I, J) <> "" Then
                If J = 3 Then Cells(I, 7) = Cells(I, J)
                If J > 3 Then Cells(I, 7) = Cells(I, 7) & ";" & Cells(I, J)  'CHANGE TO "; " IF SPACE REQUIRED
            End If
        Next J
    Next I
    End Sub
    
    Public Sub ConcateEmailsML()
    'CONCATENATE MULTI-LINE CELL
    LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
    
    For I = 1 To LastRow
        RowHt = 15
        For J = 3 To 6
            If Cells(I, J) <> "" Then
                If J = 3 Then Cells(I, 7) = Cells(I, J)
                If J > 3 Then Cells(I, 7) = Cells(I, 7) & ";" & Chr(10) & Cells(I, J)
                Cells(I, J).RowHeight = RowHt
                RowHt = RowHt + 15
            End If
        Next J
    Next I
    End Sub
    Last edited by Maudibe; 2013-12-25 at 21:54.

Posting Permissions

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