Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

migrated from stackoverflow.com Oct 24 '12 at 2:02

This question came from our site for professional and enthusiast programmers.

    
do you want to do this with a formula or with a macro? – nutsch Oct 23 '12 at 16:45

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)),"")
share|improve this answer
1  
+1. You might want to note that IFERROR is in Excel 2007 and later. – Doug Glancy Oct 23 '12 at 16:53
    
@DougGlancy Edit done. – nutsch Oct 23 '12 at 16:54
    
+1 nicely done Thomas – brettdj Oct 23 '12 at 22:37

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

share|improve this answer
    
Yea, just do this. – Stepan1010 Oct 23 '12 at 18:10
    
if you do this, use >* if you can have data after > – nutsch Oct 23 '12 at 18:31

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.

share|improve this answer

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

share|improve this answer

You must log in to answer this question.

protected by bwDraco Aug 2 '15 at 16:57

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?