Ensure accurant entry

  • Thread starter Thread starter PCOR
  • Start date Start date
P

PCOR

I have to input a great deal of data in an Excel spreedsheet
One of the col is the person's phone number.It is VERY important that the
number be entered as XXX-XXXX
(ie 3 digits followed by a hyphen and then four more digits)
What can be done to ensure that does happen
Thanks
 
Ian,

There are a few ways to approach this. You could use Data Validation -
Custom with the following formula. First select the cells, note the active
(white) cell, Data Validation - Custom, and enter the following, changing
the D2 to the active cell of your selection:

=MID(D2,4,1)="-"

To also check the correct count of digits:

=AND(MID(D2,4,1)="-",LEN(D2)=8)

This won't check for characters other than 0-9.

Or you can enter the phone number as a number (555-1234 entered as 5551234)
and format it for the dash (Format - Cells - Number - Phone number). To
ensure the correct humber of digits, and that no other characters but number
digits have slipped in, use Data Validation - Custom:

=AND(LEN(D2)=7, ISNUMBER(D2))
 
Back
Top