CODEWITHSUNDEEP
excelworksheet-function
Mahmoud
Mahmoud

Reputation: 51

Extract words from delimited string in Excel?

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

Upvotes: 5

Views: 9383

Answers (5)

Nikhil Chandra
Nikhil Chandra

Reputation: 1

There's a simple delimit expression which helps you break the strings at specific break points. In this case semi colon is the point where you'd want to break the string. All you need to do is click on DATA in the top menu and then select your column with data and then select TEXT TO COLUMN in top navigation. It will split your data at the breals specified by you and in your case it is the semi colon where you want to split your data.

I tried to post screen shots to help but the spam detector of this site doesn't allow me to. But you may always visit my hubpage http://nikhilchandra.hubpages.com/ for the same. I hope it helps :-)

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

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)

Upvotes: 3

Mike Woodhouse
Mike Woodhouse

Reputation: 52316

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

Upvotes: 1

GSerg
GSerg

Reputation: 78155

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.

Upvotes: 2

MikeAinOz
MikeAinOz

Reputation: 128

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

Upvotes: 0

Related Questions