Substitute, then substitute something else

[techiemoore]

Okay, i have no idea what I'm doing.
After some toying, i have

=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A4,FIND(" ",A4&" ",FIND("@",A4))-1)," ",REPT(" ",LEN(A4))),LEN(A4)))," ")

in order to extract email addresses from stuff like "Bhatia, Tulikaa <Tulikaa.Bhatia@domain.com>"

My problem is that i need to also remove the angled brackets.
I do not know how to SUBSTITUTE and then SUBSTITUTE something else.
Any help would be greatly appreciated.

 

[jasonb75]

OK, that formula makes no sense. What exactly are you trying to do? (Before and after examples please, if there are different types of 'before' then at least one of each type).

 

[techiemoore]

OK, that formula makes no sense. What exactly are you trying to do? (Before and after examples please, if there are different types of 'before' then at least one of each type).

The formula will take a cell containing something like "Bhatia, Tulikaa <Tulikaa.Bhatia@domain.com>" and return "<Tulikaa.Bhatia@domain.com>"

It works without issue.

What I want is for it to also remove the angled brackets.

 

[jasonb75]

Gotcha, I misread the placement of the double quotes first time. It makes sense now. Assuming that there will always be < and > in the cell

=SUBSTITUTE(MID(A4,FIND("<",A4)+1,255),">","")

 

[Fluff]

Another option assuming that the < & > will always be there

=REPLACE(LEFT(A4,FIND(">",A4)-1),1,FIND("<",A4),"")

 

[techiemoore]

Thank you so much!
But, I'm afraid i already knew that - What I dont know is how to combine both formulas.
Is there a way to both extract the email address and the angled brackets? Or, extract only the email addresses and leave out the angle brackets?

 

[Fluff]

Which of us are you talking too? Also do your string always contain the < & >? If not please supply some representative data.

 

[jasonb75]

Or, extract only the email addresses and leave out the angle brackets?

That is what my suggestion will do based on the example that you provided.

 

[techiemoore]

@jasonb75
Oh, thank you - But that seem to return "#VALUE" when i use it.

Here is the use case:

I have a string: "Bhatia, Tulikaa <Tulikaa.Bhatia@domain.com>" (without quotes)
I need Excel to extract just the email address from the string, resulting in "Tulikaa.Bhatia@domain.com" (without quotes).
The point? I have over 200 email addresses i need to extract.

 

[techiemoore]

Sorry!
I was using it wrong.
Your formula worked, @jasonb75
Sorry for the confusion.