Validation rule for phone number formatting

[sten28]

Hi guys,

I'm trying to create a validation rule that ensures that phone numbers entered under a field appear in the following format style:

0# #### ####

I can achieve this through the Custom option in Format Cells (the 0 at the front is there because Excel removes it because it thinks it's redundant) but I'm not sure how I can limit the users to only use the above format - I was thinking it might be to do with validation. Any advice?

Cheers,

Sten

 

[SkipVought]

Hi,

Check out the Phone Number format in the Special Category of Cell Format.

Skip,
[sub]
[red][/red]
[/sub]

 

[sten28]

Hey Skip,

The cell formatting isn't the issue (besides, there's no support for Australian phone number cell formats!) - I want to ensure that end users cannot enter anything but 10 digits and that it also enters the appropriate spaces.

Is it possible that it's another problem that only VB can fix?

 

[yogia]

Hi Sten:

If I understand you correctly, you want 10 numeric digits for the phone number with a blank (space) in the 3rd and 8th position of an entry of 12 characters in total. For this the following Validation rule for an entry in cell b2 should work ...

=AND(LEN(B2)=12,MID(B2,3,1)=" ",MID(B2,8,1)=" ",ISNUMBER(SUBSTITUTE(B2," ","")+0))

Is this what you are looking for? ther may be other constraints that have not been spelled out yet but can be accommodated.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[sten28]

Hi Yogia,

That works a treat! Thank you very much! Is it possible to enter a 10 digit number (i.e 0012341234) and have Excel correct the formatting and insert the spaces (so it would become 00 1234 1234) before checking for the validation?

Kind Regards,

Sten

 

[sten28]

Still having a few more issues:

The code provided works for 1 cell, but I'm trying to have it work over a range of cells. Below is what I'm trying to achieve:

=AND(LEN(I4:I65536)=12,MID(I4:I65536,3,1)=" ",MID(I4:I65536,8,1)=" ",ISNUMBER(SUBSTITUTE(I4:I65536," ","")+0))

The only difference is I'm trying do select from I4 to I65536 but any entry I make is invalid.

Any advice?


Sten

 

[sten28]

Well, not only am I convinced Excel has made it way too hard to format this, but my boss agrees to. So thankfully this isn't something I need to implement now. Thanks for everyone who assisted!

Cheers,

Sten

 

[SkipVought]

NUMBERS and TEXT in a column = BLOOD, SWEAT & TEARS.

IMHO, numbers that are IDENTIFIERS, ought not to be pure numbers -- rather they ought to be TEXT, since you will never doe arithmetic operations on them.

IMHO, a phone number ought to be entered and stored as (999)999-9999 a SSN; 999-99-9999 with appropraite data entry validation.

Something like an invoice number or employee number that has nothing by digits, ought to be preceeded in a cell by a single quote '99999999

NUMBERS and NUMERIC TEXT are two different animals. They are handled differently and when mixed, wreak havo.

Skip,
[sub]
[red][/red]
[/sub]

 

[yogia]

Still having a few more issues:

The code provided works for 1 cell, but I'm trying to have it work over a range of cells. Below is what I'm trying to achieve:


CODE
=AND(LEN(I4:I65536)=12,MID(I4:I65536,3,1)=" ",MID(I4:I65536,8,1)=" ",ISNUMBER(SUBSTITUTE(I4:I65536," ","")+0))

The only difference is I'm trying do select from I4 to I65536 but any entry I make is invalid.

Any advice?

Hi Sten:

You need to write the formula only once even for a range of cells you want to apply Vlaidation to. Let us say you want to apply the DataValidation to cells B2:B101, then do as follows ...

1) select the range B2:B101
2) with cell B2 as the ActiveCell, invoke Data|Validation
3) then in the DialogBox that pops up, use Custom
4) enter the following formula ...
=AND(LEN(B2)=12,MID(B2,3,1)=" ",MID(B2,8,1)=" ",ISNUMBER(SUBSTITUTE(B2," ","")+0))
5) click OK

DataValidation will apply to every cell in the selected range ... that is it will apply to every cell in the range B2:B101.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Sten:

I used the range of cells B2:B101 in my last post. I could have easily used your range I4:I65536, and with cell I4 as the ActiveCell use the following DataValidation formula ...

=AND(LEN(I4)=12,MID(I4,3,1)=" ",MID(I4,8,1)=" ",ISNUMBER(SUBSTITUTE(I4," ","")+0))

and the dataValidation would have to every cell in the range I4:I65536

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Yogia,

That works a treat! Thank you very much! Is it possible to enter a 10 digit number (i.e 0012341234) and have Excel correct the formatting and insert the spaces (so it would become 00 1234 1234) before checking for the validation?

Hi Sten:

This is how it will be done ...

1) select your range of interest I4:I65536 or you might want to select the entire column I
2) invoke Number|Number|Custom, and enter the following format ...

00 0000 0000

3) click OK

Now when you will enter a 10 digit number it will be displayed as

two numeric digits, then a space, four numeric ddigits, then a space, and then 4 numeric digits

I hope this helps.

NB:
if you have an entry 0012341234,
starting 0s need not be entered (meaning you could simply enter 12341234)
and
it will be displayed as

00 1234 1234




Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com