excel - Custom validation using IF() and Len() -

i made custom phone format ###-###-#### phone # can entered 5555555555 => 555-555-5555 or 555-555-55555.

i need non-vba solution i.e 1 can enter in data validation window checks cell length , validates if length 10 or 12.

i have been trying long time can not trial , error or googling.

my best guess (which not work)

=if((len(e2:e32)=12,0),( len(e2:e32)=10,0)) 

thanks

this not easy since excel not have built in pattern matching function - can use built in functions in validation checks.

there solution though!

fortunately can check validation based on state of dependent cell. that's approach we'll take here.

1) first job format telephone number text (my functions assume you've done this) excel doesn't trim leading zeros. in practice you'd format whole column way. let's assume cell a1 contain phone number.

2) validation formula ridiculously large if attempt put in 1 cell , difficult maintain. put validation stuff "off-spreadsheet"; i.e. in column that's not visible user. said, we'll use columns b,c , d clarity. (just cut , paste these elsewhere once you're done).

3) in b1 put =or(c1,d1)

4) in c1 put =iferror(if(len(a1)=10,value(a1)*0 + 1,false),false). validates format no dashes.

5) in d1 put =iferror(if(or(len(a1)=12,len(a1)=13),if(and(mid(a1,4,1)="-",mid(a1,8,1)="-"),value(left(a1,3) & mid(a1,5,3) & mid(a1,9,32767)) * 0 + 1,false),false),false). validates format dashes.

three tricks i'm using (i) iferror used write false if result otherwise #value. allows me more woolly in programming, , (ii) value(n) * 0 + 1 pattern returns 1 if n number and, conveniently compute #value , delegate surrounding function if n not number. (iii) 32767 in mid function allows compare remaining characters in string without having use more clumsy right expression. 32767 limit on number of characters in cell. perhaps i'm out 1 here; no downvotes due please ;-)

6) lastly, cell a1, choose custom validation , set =b1 validation formula.

this it! pass 3 formats:

5555555555, 555-555-5555 or 555-555-55555 you've used 5 wildcard digit.