CODEWITHSUNDEEP
excelexcel-2010validationvba
user2511875
user2511875

Reputation: 493

Data Validation In Excel to a format ###-###-###-###

Apologies if this is a common sense question, checked the web but couldn't find the exact answer I am want. I am trying to make excel only allow a number to be entered in the following format [ ###-###-###-### ] (For example, something like 102-204-304-101). I have got something similar where if I highlight a cell and go to (Format Cells -> Custom -> and in the "Type field" I enter 000"-"000"-"000"-"000. This works if somebody enters 102204304101, it will translate into 102-204-304-101. But I want another user to explicitly type the hyphens. I would guess it can be done in the "Data Validation" section under the Data Ribbon (Note I am using Excel 2010), but couldn't figure out how to do this. Would be grateful if anybody can kindly provide any tips. Thank you in advance.

Upvotes: 0

Views: 917

Answers (4)

kris
kris

Reputation: 314

If the Cell is I10

DATA >> DATAVALIDATION : CUSTOM

=IF(LEN(I10)<=12;TEXT(I10;"000-000-000-000");FALSE)

Upvotes: 0

SeanC
SeanC

Reputation: 15923

as each character has a definite range (0-9 and -) we can use the ASCII values of the characters to check the text. We also check the length, and, if the string is too short (producing error in the eqation, we wrap the whole thing in an IFERROR.

Building this up, we use CODE and MID to check each character:

Lower range: CODE(MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)) >= {48,48,48,45,48,48,48,45,48,48,48,45,48,48,48}
Upper Range: CODE(MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}, 1)) <= {57,57,57,45,57,57,57,45,57,57,57,45,57,57,57}
length: LEN(A1)=15

has to pass all tests: AND
and can't produce an error IFERROR

Putting that all together, we get:

=IFERROR(AND(LEN(A1)=15, CODE(MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15},1)) >= {48,48,48,45,48,48,48,45,48,48,48,45,48,48,48}, CODE(MID(A1, {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}, 1)) <= {57,57,57,45,57,57,57,45,57,57,57,45,57,57,57}),FALSE)

Put this in the cell next to the place you want them to enter the code (B1 in this example), and put the validation as =B1

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

Use this validation formula (replacing D11 with appropriate cell):

=IF(MID(D11;4;1)&MID(D11;8;1)&MID(D11;12;1)="---";ISNUMBER(VALUE(-0,1&MID(D11;1;3)&MID(D11;5;3)&MID(D11;9;3)&MID(D11;13;3)&1E+100)))

This ensures slashes and numbers.

You may have to change , to ; and . to , according to your local.

Upvotes: 0

pnuts
pnuts

Reputation: 59485

Based on Excel 2007: 

=AND(MID(A1,4,1)="-",MID(A1,8,1)="-",MID(A1,12,1)="-")

as a formula under Validation criteria , Allow: Custom, validates for hyphens in positions 4, 8 and 12.

Upvotes: 0

Related Questions