asked on

Return a string either side of a specific character in that string - Excel

Hi All

I have data in a spreadsheet that is exported from a guest Wi-Fi system. Users are required to enter a name and email address, or use a social media account to use the free Wi-Fi. That data is downloaded into Excel as a csv file. The issue I have is that the name and email address data are presented in the same cell as follows
"User Name (user@emailaddress.com)" - without the quotes.
To complicate matters further, for users who log in with social media accounts different data is displayed in the cell as follows:
"User Name (http://www.facebook....................etc)" - I need to ignore these logins and extract the email address ONLY and display them in another cell on the same sheet.

I have no way of altering the output from the Wi-Fi system, so must do everything in Excel.

Obviously the common denominator is the @ symbol, but my skills don't extend any further than very basic Excel functions.
All help gratefully accepted!

GrahamSkan

Is it a rule that the required email address is the last one within the brackets? How is it separated from the social media address?

Rob Henson

Assuming text values in cell A2, the following will split into two parts:

User name: =TRIM(LEFT(A2,FIND("(",A2,1)-1))
email/facebook id: =SUBSTITUTE(TRIM(MID(A2,FIND("(",A2,1)+1,LEN(A2))),")","")

This actually assumes common denominator of brackets rather than @ symbol, ie user name and then email or facebook id in brackets.

ASKER CERTIFIED SOLUTION

Rob Henson

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

Shums Faruk

I thought of adding hyperlink to converted email IDs, please replace above SeparateEmailID with below code:

Sub SeparateEmailID()
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range, cell As Range
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row
Ws.Range("B1:B" & LR).FormulaR1C1 = "=ExtractEmailFun(RC1)"
Set Rng = Ws.Range("B1:B" & LR)
For Each cell In Rng
    Application.ActiveSheet.Hyperlinks.Add cell, cell.Value
Next
Ws.Range("B1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

Please find attached revised version
Extract-Email-IDs.xlsm

Subodh Tiwari (Neeraj)

You may try the following User Defined Function to extract valid email if any in the string.
Please refer to the attached for more details....

Function getEmail(str As String) As String
Dim regEx As Object
Dim sPattern As String

Set regEx = CreateObject("VBScript.RegExp")
sPattern = "[A-Za-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-zA-Z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"

regEx.Pattern = sPattern
    If regEx.Test(str) Then
        Set matches = regEx.Execute(str)
        getEmail = matches(0).Value
    Else
        getEmail = "Not Found"
    End If
End Function

Open in new window

Extract-Email.xlsm