asked on

How to extract information in one column and output into the next one in Excel

Hi guys

I have an excel sheet with a load of data in one particular column. It has lots of email addresses ending with various domain names. In the next column, I would like to create some sort of formula that outputs the domain names from the column with the data into the associating rows so that I can then organise it by domain name. So if cell A1 says 'lisa@abc.com i would like the next column in the associating row to output 'abc.com' and then do that for all the domain names in there.

Does this make sense? If so, can you help me write the correct formula to output the content correctly?

Thanks a lot
Yashy

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

With error handling, you may try the following formula so that if there is no email in the cell (without @ symbol), the formula will return blank.

=IFERROR(MID(A1,FIND("@",A1)+1,255),"")

Open in new window

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

As far as VBA solution is concerned, you may try the below code.
The code will populate the domains from Column A into Column B.

Sub ExtractDomain()
Dim Ws As Worksheet
Dim LRow As Long
Dim EmailRng As Range, cell As Range
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set EmailRng = Ws.Range("A2:A" & LRow)
For Each cell In EmailRng
    If InStr(cell.Value, "@") > 0 Then
        cell.Offset(0, 1).Value = Split(cell.Value, "@")(1)
    End If
Next cell
Application.ScreenUpdating = True
End Sub

Open in new window

Yashy_ExtractDomain.xlsm

Yashy

ASKER

Thanks a lot for the responses fellas.

Subodh Tiwari (Neeraj)

You're welcome Yashy!