Telephone number validation in excel

[Lohith]

Can anyone tell me how to create a validation in excel for phone number of format like
1. '+46 12345678
2. '+46 12345678 #123
or so. Yes it should take ', numbers, space and #. It should not take any letters.

 

[Xiq]

Hi Lohith,

Try this:

=ISNUMBER(--MID(SUBSTITUTE(A1," ",""),2,10))

 

[oldchippy]

@Lohith

The formula doesn't appear to work, try

'+46 12345678 # Lohith

It should not take any letters

 

[Xiq]

Well, the rules are a bit fuzzy... does it need to check if it has a 10 digit? The "#123" matching with the 3-5 digits? More rules plz

 

[AIM]

Hi Lohith,

Try this:

=ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"+",""),"#","")))

Regards!!

 

[NARAYANK991]

Hi Lohith ,

With your coding skills , wouldn't RegExp be an ideal way to do it ?

Narayan