I have the following format in Excel and I am trying to extract the data between the characters:
First Last <flastname@xxxx.com>
I need to extract the email address between < and > into its own cell
Thanks!
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI have the following format in Excel and I am trying to extract the data between the characters:
First Last <flastname@xxxx.com>
I need to extract the email address between < and > into its own cell
Thanks!
use the following formula, assuming your data is in cell A2
=mid(left(A2,find(">",A2)-1),find("<",A2)+1,len(A2))
To avoid error message if your data is not conforming, you can use the following in excel 2007 and up:
=iferror(mid(left(A2,find(">",A2)-1),find("<",A2)+1,len(A2)),"")
IFERROR
is in Excel 2007 and later.
– Doug Glancy
Oct 23 '12 at 16:53
For maybe a simpler approach, or maybe just a different approach without formulas.
You can do a Replace
on the data range, twice.
First Replace *<
with (blank)
Then Replace >
with (blank)
.
Done (unless some of your data doesn't conform!!!)
The answer of nutsch doesn't work if the two characters are the same.
I think this is better:
=MID(K370, FIND("a",K370)+1, FIND("b", K370, FIND("a", K370)+1)-FIND("a",K370)-1)
where K370 is your source cell and "a" and "b" are the two consecutive chars.
you can select the column of your data and choose "text to columns" in "Data" tab in the ribbon. then choose "delimited" and click next. then in delimiters type "<" and click next. then click finish. do the same way for delimiter ">" and it's ok. note this is not the simple way bat it will learn you a soloution for other questions. have fun