在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;username@site.com;usa
B1 >> david eric;34th street;tel.543212345;name@web.net;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

请注意，电子邮件没有特定的位置，所以它可以在任何地方。此外，除了“@”之外，也没有常见的单词或字符，因此必须有一个公式来选择分号+包含的“@”来提取电子邮件，并将其放入A2和B2中等等。

5 个解决方案

#1

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

将数据复制到列a中。数据->文本到列…分隔(下一个>)分号完成

Now you have data in columns A-F.

现在你有A-F列的数据。

In G1 enter:

在G1输入:

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

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

并按Ctrl +衬衫+ Enter。阻力公式。

#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 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)

#3

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

这里有一个使用正则表达式的VBA函数:

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

#4

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

我的快速猜测是编写一个使用Regex的VBA函数，查看http://www.vbforums.com/showthread.php?

#5

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.

有一个简单的分隔表达式，可以帮助您在特定的断点处中断字符串。在这种情况下，semi冒号是你想要破坏字符串的点。你所需要做的就是点击顶部菜单中的数据，然后用数据选择你的列，然后选择文本到顶部导航栏。它将在您指定的breals处分割您的数据，在您的例子中，它是您想要分割数据的semi冒号。

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 :-)

我试着用屏幕截图来帮助，但是这个网站的垃圾邮件检测器不允许我这样做。但你也可以访问我的hubpage http://nikhilchandra.hubpages.com/。我希望它有帮助:

#1