Extract words from delimited string in Excel?

Question

I need to extract certain words between the semicolon and the condition that these words contain "@", which means emails.

Here is an example:

A1 >> james john;Paris street;po box:12345;tel.[phone]; [e-mail] ;usa B1 >> david eric;34th street;tel.[phone]; [e-mail] ;canada;ottawa 

... etc.

Please note that there is no special place for email so that it can be anywhere. There are also no common words or characters except “@”, so there should be a formula for choosing between a semicolon + contain “@” to extract the email and put it in A2 and B2, etc.

Answer 1

Copy the data to column A
Select data.
Data → Text to Columns...
Delimited ( Next > )
Semicolon
Finish

You now have the data in the AF columns.

In G1 enter:

 =INDEX(A1:F1,1,MATCH("~",IF(ISNUMBER(FIND("@",A1:F1)),A1:F1),-1)) 

and press Ctrl + Shirt + Enter. Drag the formula down.

Answer 2

 B1 =FIND("@",A1) C1 =IF(ISERR(FIND(";",A1,B1)),LEN(A1)+1,FIND(";",A1,B1)) D1 =MAX(IF(ISERR(FIND(";",LEFT(A1,C1-1),ROW(INDIRECT("A1:A"&B1)))),0,FIND(";",LEFT(A1,C1-1),ROW(INDIRECT("A1:A"&B1))))) E1 =MID(A1,D1+1,C1-D1-1) 

You can combine them into one super formula if you want.

 B1 = the location of the at sign C1 = the first semicolon after the @ D1 = the semicolon before the at sign (array entered) 

Answer 3

Here's the VBA function that uses the regex:

 Function EmailFrom(source As String) Dim Matches As Object With CreateObject("VBScript.RegExp") .Pattern = "(^|;)([^;@] +@ [^;@]+);?" If .Test(source) Then Set Matches = .Execute(source) EmailFrom = Matches(0).SubMatches(1) Else EmailFrom = CVErr(xlErrNA) End If End With End Function 

[update] or even (compressed)

 Function EmailFrom(source As String) With CreateObject("VBScript.RegExp") .Pattern = "(^|;)([^;@] +@ [^;@]+);?" With .Execute(source) If .Count > 0 Then EmailFrom = .Item(0).SubMatches(1) Else EmailFrom = CVErr(xlErrNA) End If End With End With End Function 

Answer 4

My quick guess is to write a VBA function that uses Regex, check out http://www.vbforums.com/showthread.php?t=480272

Answer 5

Here's a simple expression that helps break lines at specific break points. In this case, half the colon is the point at which you want to split the line. All you have to do is click “DATA” in the top menu, and then select the column with the data, and then select “TEXT TO COLUMN” in the top navigation. It will break your data into the violations you indicated, and in your case it will be a colon where you want to split your data.

I tried to post screenshots to help, but the spam detector on this site does not allow me. But you can always visit my hubpage http://nikhilchandra.hubpages.com/ for her. Hope this helps :-)