Reputation: 781
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
Reputation: 3277
Another attempt using FILTERXML if you are using one of the following versions of Excel:
=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
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
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.
Upvotes: 1
Reputation: 1
paste in B2:
=REGEXEXTRACT(A2, "<(.*)>")
and arrayformula would be:
=ARRAYFORMULA(IFERROR(REGEXEXTRACT(A2:A, "<(.*)>")))
Upvotes: 2