Data validation with multiple conditions (OR)

[Olia2]

Hi guys,

I hope you can help me, as I've tried many different options and am out of options (excel will not accept my formula).

I am trying to construct data validation *custom*, where user can only insert phone number OR email address; this is what I have figured out so far:


=OR(A1=ISNUMBER(MATCH("*@*.?*",A1,0))),(A1=(ISNUMBER(A1),LEN(A1)=10))

Could anyone point me where I am making the mistake? I've been trying to figure this out for the past 2 hours.

Thanks a million in advance!

 

[Gerald Higgins]

OK let's break this into two sections.

Dealing with the phone number bit first, this formula will only allow input of a 10 digit number
=AND(ISNUMBER(A1),LEN(A1)=10)

For the email address, exactly what criteria do you want to use, to determine that the input is an email address ?
Do you simply want to check that the string of characters contains a "@" symbol, or is it more complicated ?

 

[Olia2]

Hi Gerald,

Thank you for your response.

Regarding your questions:
1. Yes, only 10 digits should be allowed
2. The point of my formula was verify that there is @ symbol in the text string, and ". + n characters" behind it;

I hope it's clear

And huge thank you.

 

[Gerald Higgins]

Do you need to define n or can it have any value ?

Have you tested my proposal in post #2 to check that it handles telephone numbers the way you want ?
For example, my proposal WILL allow input of a number such as 1234.56789
And it will NOT allow input of a number such as 0123456789
Is this OK ?

 

[Olia2]

n can have any value, as different email domains can be used.

Telephone number formula is tested and is OK

 

[Gerald Higgins]

OK, try this . . .

=OR(ISNUMBER(SEARCH("?@?.?",A1,1)),AND(ISNUMBER(A1),LEN(A1)=10))

Input of Z@Z.Z will be allowed.
Input of Z@Z will not be allowed.

 

[Olia2]

Hi Gerald,

amazing thanks! I adjusted the formula a bit to fit my purposes (your suggestion would not allow me to enter the email address), the only thing missing: the formula does not allow the number to start with 0 (zero) - excel formats it in number and thus removes the 0 turning the number in 9 digits.

Is there any trick to keep the formula and add possibility for phone number to start with zero?

=OR(ISNUMBER(MATCH("*@*.?*",A1,0)),AND(ISNUMBER(A1),LEN(A1)=10))

 

[Gerald Higgins]

If you want to allow numbers starting with zero, you're probably going to have to store them as text strings.

You can still use data validation to check that what is input is actually a number in a text format, for example . . .
=OR(ISNUMBER(MATCH("*@*.?*",A1,0)),AND(ISNUMBER(A1+0),LEN(A1)=10))