Validating Phone Numbers (including international) without VBA?

[TheWaterbug]

I have a form for entering customer information, and I want to validate whether an entry is a valid phone number. I need to accommodate international phone numbers as well as differences in formats. Most of the time we'll be copying/pasting stuff into this field, so I do _not_ want to force users to manually re-type or edit entries unless they're actually wrong, e.g. I want to accept:

+1-310-555-1212
(310) 555-1212
310.555.1212
310/515-1212
+44 1234 5678

but I want to reject typos and:

310-555-121a
310-555-1212 x24

(the latter because I've a separate field for the extension, if any.)

The phone numbers will not be processed by any dialer; they just need to look like valid numbers. I don't need to validate the number of digits vs. country code, because that's just way too complicated for a cell formula, and in some countries (like China) it's very highly variable, and depends on the city code.

I've come up with an array formula that just returns the number of characters that are not included within a literal array of valid characters:

{=SUM(ISERROR(MATCH(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1),{"0","1","2","3","4","5","6","7","8","9","0"," ","-",".","(",")","/","+"},0))*1)}

<tbody>
</tbody>

If I put that in a dummy cell I can then use that as Conditional Format to highlight the Phone Number cell (A4) if it contains any illegal characters. I can also use another dummy cell to strip the non-numeric characters and test for len() <= 15, which I believe is the limit for valid international phone numbers.

Things I don't like about it:

• It's an array formula, so I can't use it directly in a Conditional Format or Data Validation (AFAIK)
• Requires 2 dummy cells if I also want to test for length

I do NOT want a VBA solution, because we email this sheet to customers frequently, and I don't want security warnings popping up. This needs to be a straight Excel formula.

Thanks!

 

[pbornemeier]

With a big hint from this thread: http://www.mrexcel.com/forum/excel-questions/137950-data-validation-only-allowing-numbers-si.html

This strips out your undesired characters (space, dash, open parenthesis, close parenthesis, slash, period) and gives you the length:
=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D4," ",""),"-",""),"(",""),")",""),"/",""),".","")+0)

You could use this for data validation or conditional formatting on the cells:
=ISNUMBER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1," ",""),"-",""),"(",""),")",""),"/",""),".","")+0)