1

I want to extract the top-level domain from e-mail addresses using Excel formulas.

I tried it first with concatenating RIGHT(..) Formulas and splitting for the dot. Sadly I do not know how to do this recursively with excel formulas, so I swapped to deleting all characters except the last 4. Now the problem is, when I split my formulas into single cells it works perfectly fine. If I try to use them together, I get only the output of the first inner Formula. How do I fix this?

=RIGHT(B8; LEN(B8)-(LEN(B8)-4))
=RIGHT(BF8;LEN(BF8)-FIND(".";BF8))

These are the formulas split into single cells. And here both together

=RIGHT(RIGHT(B8; LEN(B8)-(LEN(B8)-4));LEN(B8)-FIND(".";B8))

I get the same return value as in the first row from this formula

=RIGHT(B8; LEN(B8)-(LEN(B8)-4))
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Patrick.H
  • 535
  • 3
  • 6
  • 21

2 Answers2

1

This =RIGHT(B8; LEN(B8)-(LEN(B8)-4)) is just a uselessly complicated version of =RIGHT(B8; 4).

Substituting this for BF8 in 

=RIGHT(BF8;LEN(BF8)-FIND(".";BF8))

yields this

=RIGHT(RIGHT(B8; 4);LEN(RIGHT(B8; 4))-FIND(".";RIGHT(B8; 4)))

which can be simplified as

=RIGHT(RIGHT(B8; 4);4-FIND(".";RIGHT(B8; 4)))

So that's the answer to your question.

But note that this will fail when parsing e-mail addresses whose top-level domain name has more than 3 characters! So it won't work for e.g. test@test.info. Note that top-level domains can be up to 63 characters long!

In this earlier answer, I give a more general solution to this problem, not limited to searching a predetermined number of characters from the right.

=MID(B8;FIND(CHAR(1);SUBSTITUTE(B8;".";CHAR(1);LEN(B8)-LEN(SUBSTITUTE(B8;".";""))))+1;LEN(B8))

returns everything after the last . in the string.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
0

Dot character may appear in left part if e-mail, like: john.johnson@email.com So, you can't just find "." you need firstly find @, then find dot in right substring. Tehese are your steps:

1. =FIND("@"; B8)
find @ character place

2. =RIGHT(B8;LEN(B8) - FIND("@"; B8))
get substring right from @

3. =FIND(".";RIGHT(B8;LEN(B8) - FIND("@"; B8)))
find "." in step 2 substring

4. =RIGHT(RIGHT(B8;LEN(B8) - FIND("@"; B8)); LEN(RIGHT(B8;LEN(B8) - FIND("@"; B8))) - FIND(".";RIGHT(B8;LEN(B8) - FIND("@"; B8))))
get right(step2; len(step2) - step3)
Vmesser
  • 46
  • 4