Extract words from delimited string in Excel?

Question

I need to extract specific words between semicolon with a condition that these words contains "@" which mean e-mails.

Here is an example:

A1 >> james john;Paris street;p.o. box:12345;tel.987654321;[email protected];usa
B1 >> david eric;34th street;tel.543212345;[email protected];canada;ottawa

... and so on

Notice that there are no specific place for the email so it could be anywhere. Also there are no common words or characters except "@" so there must be a formula to choose between semicolon + contain's "@" to extract the e-mail and put it in A2 and B2 and so on

Answer 1

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

Answer 2

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

Now you have data in columns A-F.

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 3

Here's a VBA function that uses a regular expression:

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 (condensed)

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

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 those into one superformula if you like.

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