how to remove extra special character

[Rhon Alvarez]

Hi Team,

need help please!

I need to extract the email address only from a certain strings of words

Ronaldo Alvarez <ronaldo.alvarez@xxx.com>

used: =LEFT(B1,FIND(" <",B1)) to extract the name only
then used: =RIGHT(B1,LEN(B1)-LEN(LEFT(B1,FIND(" <",B1)))) to supposedly extract email address only
however, I am ending with this: <ronaldo.alvarez@xxx.com>

how would I omit the two extra characters on front "<" and at the back ">" so it would only show "ronaldo.alvarez@xxx.com"

Thanks in advance.

Rhon

 

[Peter Bartholomew]

Seems you were just about there. Simply a matter of adjusting the counts.

=LET(
  openBr,  FIND("<",email),
  closeBr, FIND(">", email),
  MID(email,1+openBr, closeBr-openBr-1))
 
= MID(email,
    1+FIND("<",email),
    FIND(">",email)-FIND("<",email)-1)

The first is my preferred option, the second with older versions of Excel.

 

[GraH - Guido]

You can use MID(B1, find("<", B1)+1, len(B1)-len(left(B1, find("<", B1)+2))))

On my phone without excel, so maybe the formula needs some adjustments.

 

[bosco_yip]

Or,

=MID(B1,FIND("<",B1)+1,LEN(B1)-FIND("<",B1)-1)

Regards

 

[Rhon Alvarez]

You can use MID(B1, find("<", B1)+1, len(B1)-len(left(B1, find("<", B1)+2))))

On my phone without excel, so maybe the formula needs some adjustments.

Hi GraH - Guido,

This works well, i just need to adjust the last portion of the formula from "+2" to "+1".

 

[Rhon Alvarez]

Or,

=MID(B1,FIND("<",B1)+1,LEN(B1)-FIND("<",B1)-1)

Regards

Hi bosco_yip,

This was a great alternative.

Thanks as always

 

[pecoflyer]

There's more than one way to skin a cat
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"<",""),">",""))