Validating email address formatting

[Guest]

Is there an easy formula to tell you if an email address is formatted
correctly, with (e-mail address removed)? I don't even need to know more than that
(such as valid domains, etc.) for now.

My original thought was just a basic IF statement to drop in to the next
column that tells me if the cell is formatted correctly. But I don't know
how to look for text in a particular sequence or format.

Any ideas for something like this, or better? Thanks!
(You all always have the best answers!)

-bethp

(Inadvertently x-posted to Excel Programming too; sorry for the dupe request!)

 

[ScottO]

It shouldn't be too hard to construct the "test formula", but I think that
the starting point is to identify what the tests are going to be.

I've got a few to propose:
1. Test that it's a text string
2. Test for the position of "@" in the text string (A)
3. Test for the position of "." in the text string (B)
4. Test that neither A or B are NA#
5. Test that there is text before A
6. Test that there is text between A and B
7. Test that there is text after B

There's probably some others that I haven't thought of (and maybe some of
mine are redundant).
Given the number of tests, it's probably worthwhile making one formula for
each test and then getting the answer with a summary formula.

Assuming the eddress is in A1, and the formulas go in cells B1, C1, etc.,
the try these:
1. B1 =IsText(A1)
2. C1 =Find("@",A1)
3. D1 =Find(".",A1)
4. E1 =SUM(C11)
5. F1 =C1>1
6. G1 =D1-C1>1
7. H1 =Len(A1)>D1

The summary formula (in I1) could then be something like =AND(B1:H1), where
a result of False means that at least one of the tests has failed.

It's a bit long winded, and may need some de-bugging, but it should get the
job done.
Rgds,
ScottO


| Is there an easy formula to tell you if an email address is formatted
| correctly, with (e-mail address removed)? I don't even need to know more than that
| (such as valid domains, etc.) for now.
|
| My original thought was just a basic IF statement to drop in to the next
| column that tells me if the cell is formatted correctly. But I don't know
| how to look for text in a particular sequence or format.
|
| Any ideas for something like this, or better? Thanks!
| (You all always have the best answers!)
|
| -bethp
|
| (Inadvertently x-posted to Excel Programming too; sorry for the dupe
request!)

 

[Guest]

Thank you Scott! This got me most of the way there, though it isn't appear
foolproof yet. (Gives me something to play with to test further though)

So far, gauging the validity of the address based on whether there is a dot
and where it is isn't panning out. We have several "(e-mail address removed)"
addresses. The text-placement tests can fail in this case, and an incomplete
email address such as "john.doe@company" will pass.

I suppose putting in a test to ensure there are no spaces in the address
would help too, preventing "john (e-mail address removed)" from passing.

I can't wait until we invest in a list server to manage all of this! ;-)

Thanks again. This gives me something to start with!
-bethp

:

 

[ScottO]

Sorry, didn't think about the (e-mail address removed) case ...
Change the formula for finding the "." to =FIND(".",A1,C1+1) which will
force it to start searching for the "." after the point where the "@" was
found.
Rgds,
ScottO

| Thank you Scott! This got me most of the way there, though it isn't
appear
| foolproof yet. (Gives me something to play with to test further though)
|
| So far, gauging the validity of the address based on whether there is a
dot
| and where it is isn't panning out. We have several "(e-mail address removed)"
| addresses. The text-placement tests can fail in this case, and an
incomplete
| email address such as "john.doe@company" will pass.
|
| I suppose putting in a test to ensure there are no spaces in the address
| would help too, preventing "john (e-mail address removed)" from passing.
|
| I can't wait until we invest in a list server to manage all of this! ;-)
|
| Thanks again. This gives me something to start with!
| -bethp
|
| "ScottO" wrote:
|
|

 

[Guest]

Hi Scott,
Thanks again. With your help and a couple other posts in this board, I was
able to hack this together! Here is what I have:

1. Test that it's a text string
ISTEXT(A2)
2. Test for the position of "@" in the text string (A)
FIND("@",A2)
3. Count occurrences of "@" in test string (nullify if <>1)
AND((SUMPRODUCT(--(LEN(A2)-LEN(SUBSTITUTE(A2,"@","")))))=1)
4. Test for the position of "." in the text string (B)
FIND(".",A2,C2+1)
5. Test that neither A or B are NA#
SUM(C2:E2)
6. Test that there is text before A
C2>1
7. Test that there is text between A and B
E2-C2>1
8. Test that there is text after B
LEN(A2)>E2
9.Does not contain a space
ISERR(NOT(FIND(" ",A2)))
a result of False means that at least one of the tests has failed
IF(ISERR(AND(B2:J2)),"",(AND(B2:J2)))

 

[Guest]

Hi,

Could you please explain how to enter your results within a cell to validate
for inputting correct address. I would really appreciate an email response,
candid, I know but I am really stuck on this one.

Kind Regards,

Gerry