Find text across multiple columns and return value in a different column

Question

I have 5 columns with email addresses. Each cell of each column contains 1 email address and the domains of the emails on each row don't repeat. I want to find only the emails containing "@gmail.com" and copy the value of that cell to a new column.

Using this can check only 1 column =IF(ISNUMBER(SEARCH("@gmail.com",T1)),T1,"")

I need it to check all 5 columns and return the value of the cell containing "@gmail.com"

Do you mean that the gmail domain is only in one of the five columns? Are you open to a VBA solution? — Brian
@Brian Yes, the gmail domain appears only once per row. I am open to a VBA solution, thank you! — Mike Athi

Brian Brian · Accepted Answer · 2016-10-19T13:49:34

This works but you'll need to adjust it to your ranges:

Option Explicit

Sub test()

Dim myRow As Range
Dim r As Range

Set myRow = Range("A1:C1")
Do While myRow.Resize(1, 1) <> ""
    For Each r In myRow
        If InStr(1, r, "@gmail.com") Then
            myRow.Offset(0, 3).Resize(1, 1) = r.Value
            Exit For
        End If
    Next r
    Set myRow = myRow.Offset(1, 0)
Loop

End Sub

Find text across multiple columns and return value in a different column

2 Answers