Extract email address from one cell and put the address into another cell

[dangileri]

Hello - I have a spreadsheet where column A is a paragraph of text such as:

sent to: abc@xyx.com

received from: 1234@myco.com

Text here....

or sometimes the cell has contents such as:

sent to: xxx@ourcompanycom received from: xxx@mail.com Hello, I am looking for.. and so on.

Looking for a formula to extract the email address (regardless of length) after the "from:" and put the output into column B

I have used: =TRIM(LEFT(SUBTITUTE(TRIM(MID(A1,FIND("from:",A1)+1,100)," ",REPT(" ",100),1),100)) With mixed results looking to capture the from: email address regardless of length - any help would be greatly appreciated.

Regard,
dangileri

 

[NeonRedSharpie]

=MID(F21,FIND("from: ",F21)+6,FIND(" ",F21,FIND("from:",F21)+6)-FIND("from: ",F21)-6)

Where F21 is the message. Maybe?

 

[Jonmo1]

Welcome to the board.

try
=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,SEARCH("from:",A1)+5,"")," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[dangileri]

=MID(F21,FIND("from: ",F21)+6,FIND(" ",F21,FIND("from:",F21)+6)-FIND("from: ",F21)-6)

Where F21 is the message. Maybe?

Thank you for the quick response - it works, but it is picking up the first word of the next sentence (as well as the email address) - It may be that a space is not imbedded in the data populating the spreadsheet???

 

[dangileri]

Thank you for the quick response, it works, but is picking up the first word after the email address - which may be an issue with the data populating the spread sheet? I'll look into that next.

 

[Jonmo1]

Yep, if the character after the email address is NOT a space, that would cause both formulas to get the next word.

we need to know what character that actually is after the email address.
Count the number of characters from the beginning up to that character after the email address.
Let's say it's the 25th character in the string.
What does this return

=CODE(MID(A1,25,1))

 

[dangileri]

Yep, if the character after the email address is NOT a space, that would cause both formulas to get the next word.

we need to know what character that actually is after the email address.
Count the number of characters from the beginning up to that character after the email address.
Let's say it's the 25th character in the string.
What does this return

=CODE(MID(A1,25,1))

It shows a line feed (ascii 10)

dangileri

 

[Jonmo1]

Do ALL of them have that char 10 after the email address?

Try

=REPLACE(LEFT(A1,FIND(CHAR(10),A1)-1),1,SEARCH("from:",A1)+5,"")

 

[dangileri]

Do ALL of them have that char 10 after the email address?

Try

=REPLACE(LEFT(A1,FIND(CHAR(10),A1)-1),1,SEARCH("from:",A1)+5,"")

Inconsistent, so I am going to see if I can get the source updated to always have a space after the email address.

Thank you again for the quick replies,
dangileri

 

[Jonmo1]

In that case, this will work if the Char 10 is there or not...

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(REPLACE(A1,1,SEARCH("from:",A1)+5,""),CHAR(10)," ")," ",REPT(" ",LEN(A1))),LEN(A1)))

Although, correcting it at the source is probably the better option...