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

dangileri

New Member
Joined
Aug 25, 2014
Messages
5
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
=MID(F21,FIND("from: ",F21)+6,FIND(" ",F21,FIND("from:",F21)+6)-FIND("from: ",F21)-6)

Where F21 is the message. Maybe?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

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

dangileri

New Member
Joined
Aug 25, 2014
Messages
5
Code:
=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

New Member
Joined
Aug 25, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Aug 25, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Aug 25, 2014
Messages
5
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,747
Messages
5,597,901
Members
414,187
Latest member
pdida8

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top