Extract words from delimited string in Excel?

excel, worksheet-function

Solution

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)

Problem

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

Original source