CODEWITHSUNDEEP
regexgoogle-sheetsexcel-formulagoogle-sheets-formulaarray-formulas
Jade Cacho
Jade Cacho

Reputation: 781

How to get the email address in between 2 different characters in Excel or Google Sheets using formula only?

The cell A2 has the following sample email address:

Jose Rizal <[email protected]>

I want to get the email address only in cell B2:

=right(A2,len(A2) - search("<",A2,1))

but the result was: [email protected]> (with the > on the last character).

The table looks like this and the expected result is on B2:

  |            A                |          B         |
 1| complete email address      | email address only |
 2| Jose Rizal <[email protected]> | [email protected]     |

What to improve on my formula?

Upvotes: 2

Views: 188

Answers (4)

Terry W
Terry W

Reputation: 3277

Another attempt using FILTERXML if you are using one of the following versions of Excel:

FILTERXML

=FILTERXML("<b><a>"&SUBSTITUTE(LEFT(A2,LEN(A2)-1),"<","</a><a>")&"</a></b>","//a[2]")

Suppose your data stars from Cell A2, drag the formula down to apply across.

For the logic behind this formula you may give a read to this article: Extract Words with FILTERXML.

Upvotes: 1

K753
K753

Reputation: 388

Add another Left trim in there:

=LEFT(RIGHT(A2,LEN(A2) - SEARCH("<",A2,1)),LEN(RIGHT(A2,LEN(A2) - SEARCH("<",A2,1)))-1)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

In Excel, or Google sheets(But player0's REGEXEXTRACT is better to use in Google Sheets):

=MID(REPLACE(A2,FIND(">",A2),LEN(A2),""),FIND("<",A2)+1,LEN(A2))

And drag the formula down.

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

paste in B2:

=REGEXEXTRACT(A2, "<(.*)>")

0

and arrayformula would be:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A, "<(.*)>")))

Upvotes: 2

Related Questions