Extracting the correct email address

[SandsFoy]

Hi All,

I have a report where I have a customers surname in column B, and then four different email addresses are entered in columns K, L, M and/or M. During a booking journey the same email address can be put into each column, and more than one email address can be put into a column by splitting it with a ";".

=IF(ISERROR(SEARCH($B1,K1)),IF(ISERROR(SEARCH($B1,L1)),IF(ISERROR(SEARCH($B1,M1)),IF(ISERROR(SEARCH($B1,N1)),"",N1),M1),L1),K1)

I have developed this formula to extract all the data from cell if it find the customers surname in that cell. I have then been able to identify if there is more than 1 email address in that cell returned with the below.

=LEN(K2)-LEN(SUBSTITUTE(K2,"@",""))

In a cell I can have Alan.smith@test.com;mona.lively@test.com - the surname is Smith. So I can extract the contents of the cell based on the formula finding Smith, and I can identify there are two emails addresses present (because there are 2 @ symbols).

The next step is for the formaula to only display the alan.smith@test.com - and nothing else. I'm sure this is possible, but I can't wrap my head around it.

Can anyone help?
Thanks,

 

[Special-K99]

Different approach
Using Helpers (Not really Helpers but cells at each stage of the process)

1. Concatenate all the email addresses, separate them by a semi colon
in O1
=K1&";"&L1&";"&M1&";"&N1

2. Find the surname in the concatenated emails
in O2
=SEARCH(B1,K1&";"&L1&";"&M1&";"&N1)

3. Find how many semi colons occur before that position and add 1
in O3
=LEN(LEFT(O2,O3))-LEN(SUBSTITUTE(LEFT(O2,O3),";",""))+1
This will give you the nth email address to extract

4. Use that nth number in this formula to extract the email address
=TRIM(MID(SUBSTITUTE($O1,";",REPT(" ",LEN($O1))),(O3*(LEN($O1))-LEN($O1))+1,LEN($O1)))

or you could just do this without helpers

=TRIM(MID(SUBSTITUTE(K1&";"&L1&";"&M1&";"&N1,";",REPT(" ",LEN(K1&";"&L1&";"&M1&";"&N1))),((LEN(LEFT(K1&";"&L1&";"&M1&";"&N1,SEARCH(B1,K1&";"&L1&";"&M1&";"&N1)))-LEN(SUBSTITUTE(LEFT(K1&";"&L1&";"&M1&";"&N1,SEARCH(B1,K1&";"&L1&";"&M1&";"&N1)),";",""))+1)*(LEN(K1&";"&L1&";"&M1&";"&N1))-LEN(K1&";"&L1&";"&M1&";"&N1))+1,LEN(K1&";"&L1&";"&M1&";"&N1)))

 

[SandsFoy]

Wow, that will take me some time to decipher......but thanks!