Data Validation... Only Numbers, Hyphens and N/A

[dparteka]

I'm using the formula below in a data validation and it works fine. It allows only numeric value entries with the one exception of an upper case "N/A". I've been trying to expand on this to also include the use of hyphens. So, legitimate entries would be an uppercase N/A or any number combination with or without a hyphen(s). Thank you for looking and I appreciate the help.

Some examples:
N/A
123456
12345678
1-123456
123-45678
123456-8
123-456-789-1

Current data validation formula being used:
=OR(AND(EXACT(W2, UPPER(W2)), W2 = "N/A", ISTEXT(W2)), ISNUMBER(W2))

 

[GraH - Guido]

Sorry, I'm confused by your sentence. First N/A is not allowed, but then it is?
"with the one exception of an upper case "N/A"" versus "legitimate entries would be and uppercase N/A"
Or is the problem that it is working, except for the N/A and it should work?
Care to clarify?

 

[bobhc]

You are trying to walk through a vat of honey, very sticky.
Format your cells as text or you could put a single quote ' before your hyphen/dash.

 

[Chihiro]

There's a lot of redundancies in below part of your formula.
EXACT(W2, UPPER(W2)), W2 = "N/A", ISTEXT(W2)

Your current formula can be simplified to...
=EXACT("N/A",W2) + ISNUMBER(W2)
Or to...
=OR(EXACT("N/A",W2),ISNUMBER(W2)

So your new validation formula can be...
=EXACT("N/A",W2)+ISNUMBER(SUBSTITUTE(W2,"-","")*1)

 

[GraH - Guido]

You are trying to walk through a vat of honey, very sticky.

Love that expression... Coming here not only helps me in Excel, but also to improve my English.

 

[NARAYANK991]

Hi ,

Would you want to exclude text strings such as :

123----45678

Narayan

 

[dparteka]

Wow you guys are awesome, great responses, let me address each one individually.

GraH-Guido... I am also a bit confused and probably not qualified enough to completely answer your question about allowing and then not allowing. The formula in my original message does work but not if I include the hyphen.

bobhc... yes sir, it's sticky. When reading your response I thought to myself, duh could it be that simple and it turns out that it is, well kind of. The only problem is that the cell must only except (1) numeric values, (2) an uppercase N/A or (3) a hyphen. Your suggestion would allow A1234, 1234A, 12A34, ABCD and so on.

Chihiro... your formula works perfectly, excluding the issue that Narayan pointed out.

Narayan... now that is something I had not thought about; multiple hyphens nested together should not be allowed.