Extract EmailID from list in Excel

Most of the time it happens to extract email ids from the list in excel, we have large size of list and that becomes difficult task to get the email ids accurately. 

In Excel in-built text function also can be used to extract email IDs. 

TEXT Functions like LEFT RIGHT, LEN, FIND, SEARCH, REPT, REPLACE functions can also help you to extract email ids.

Here is an example to extract email IDs in nested formula as well as VBA Macro codes.

EXCEL - TEXT FUNCTIONS helps us to extract string values as per our requirement. In above example I have applied advance excel nested function to extract the email IDs and same converted into VBA coding dynamically can be executed.

Now let us understand the nested formula used:

TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",LEN(A2))),LEN(A2)))

SUBSTITUTE(LEFT(A2,FIND(" ",A2&" ",FIND("@",A2))-1)," ",REPT(" ",LEN(A2))),LEN(A2)

Using substitute we look for spaces (" ") and special character ("@") in the given string for repetitive times. Once these are identified we TRIM and remaining part which is not spaces we fetch out of the string.

Practicing will help you to build nested formula in excel.

Further you can convert the formula in macro coding...

 To write the code we have to declare variables which will hold the values into it.

Press Alt + F11 key to open VBA Editor

Type sensible name to your procedure.

Declare variable like I have written in above images. I have declared 3 variable of which:

PreviousCellVal with Data Type as Variant
                     This variable is variant because the string may contain numbers, special 
                     characters or alphabets.
Lastrow with Data Type as Long
                     This variable will hold the range used in sheet, to identify last used row or cell 
                     which is not known to us and so declared as Long
i with Data Type as integer
                     This will hold numeric value and same will be used in Loop to assign the 
                     incremental value as row number in For-Next Loop

Using cell properties of column "A" we will get last used row as numeric value.

Using the advance excel formula in VBA let us see the trick

Since TRIM, Substitute, RIGHT, LEFT, FIND, LEN, REPT are worksheet function, you need to write correct syntax
Application.WorksheetFunction and function.

Hope this will help you, how to write formula in VBA and also extracting Email IDs through VBA.