Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

4 Answers 4

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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.