data validation

[Al]

Hi
using the Data validation function, is it possible to check people entering
into a cell telephone numbers that start 07 or 09

Otherwise what's the best way to do this please

thanks
Al

 

[Guest]

Hi
Format your cell as text first (otherwise you'll lose your leading 0s) and
then use a custom validation of:
=OR(LEFT(A2,2)="07",LEFT(A2,2)="09")

Hope this helps.
Andy.

 

[Guest]

Hi

I am a new user, and want to use Excel Data Validation within a column to
restrict the entry of phone numbers beginning with 07 and 09, I have tried
Andy's fix but it does not appear to work.

I would very much appreciate some help.

Thank you

 

[Guest]

Hi
It works for me!
Select A2, select Data/Validation. In the Allow box select Custom. In the
Formula box type:
=OR(LEFT(A2,2)="07",LEFT(A2,2)="09")

What results do you get when you try it?

Andy.

 

[Guest]

Hi Andy

Thank you very much for your reply, very much appreciated.

I have an existing telephone list that does not include any of the numbers I
want blocked, I live in the UK and the usaual format for phone numbers is a
five digit area code followed by a six digit number e.g. 01752 216547. All
numbers start with a zero.

Because of this, I want all zero prefixes to be allowed, except 07 and 09
which are mobiles and premium rates.

I highlighted the column, selected Data then Validation, and entered your
formula as suggested using the first available cell (D2 in my case) for the
formula, but when I try to enter in a restriced 07 prefix it allows me to do
so.

Thank you once again,

Paul

 

[Roger Govier]

Hi Paul

You need to wrap Andy's formula in a NOT() function to exclude them
=NOT(OR(LEFT(A2,2)="07",LEFT(A2,2)="09"))
Don't forget to format as Text first as Andy says, otherwise you loose
the leading zero.

 

[Guest]

Thank you very much Roger, could not make the formula work as Andy has
written it. Will test your suggestion soon.

Thank you once again Andy and Roger.