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

#### dangileri

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

sent to: abc@xyx.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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Code:
``=MID(F21,FIND("from: ",F21)+6,FIND(" ",F21,FIND("from:",F21)+6)-FIND("from: ",F21)-6)``

Where F21 is the message. Maybe?

Welcome to the board.

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

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???

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.

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))

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

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,"")

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

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...

Replies
9
Views
328
Replies
11
Views
694
Replies
2
Views
335
Replies
2
Views
347
Replies
1
Views
1K

1,203,606
Messages
6,056,279
Members
444,854
Latest member
goethe168

### 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.

### Which adblocker are you using?

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

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