Excel 2016 splitting a string with several spaces

Question

I have the following strings in two cells in Excel 2016

Joe Blogger [email protected] 0198156374
Martin Son [email protected] 9847493933

I want to extract just the email address from each cell, I'm struggling to find the solution.

So far I have managed to use =RIGHT to remove just the first name from either cell

I then copied and pasted the cell values and thought I'd I repeated the formula but it just doesn't work. I keep getting #VALUE error.

What am I missing / doing wrong?

I cant use the inbuilt function to split at fixed length because the split occurs at different positions in each cell , I have 167 cells.

Answer 1

This method returns the email address, testing for it assuming it will always contain an @ and will be separated by spaces from the rest of the string:

=TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM($A1),FIND(" ",TRIM($A1),FIND("@",TRIM($A1)))-1)," ",REPT(" ",99)),99))

• It looks for the first space after the @
• Return everything before that last space
• Then return everything after the last remaining space, which would be the space before the @, or the email address

If you should have other substrings in the string that contain @, or if the email is not bounded by spaces, you will need a different algorithm

The above is insensitive to how many names precede the email address, so long as there is at least one.

Answer 2

try,

=trim(mid(substitute(a2, " ", rept(" ", len(a2))), len(a2)*2, len(a2)))

A close look at the maths within the formula should reveal that adjusting the start point and length of the MID function should be able to peel out any of the sections of information.