validating telephone format

[Theo]

I need to validate that telephone numbers are entered in this format:
###-###-####.

I found something that was close
=AND(LEFT(A2,1)="C",LEN(A1)=16,ISNUMBER(SUBSTITUTE(RIGHT(A2,14),"-","")*1),LEN(SUBSTITUTE(RIGHT(A2,13),"-",""))=12) (
it validates this format:
C-###-#####-####.
But I have not been able to successfully adapt the above to what I need.
Any help would be appreciated.

 

[Per Erik Midtrød]

I need to validate that telephone numbers are entered in this format:
###-###-####.

I found something that was close:
=AND(LEFT(A2,1)="C",LEN(A1)=16,ISNUMBER(SUBSTITUTE(RIGHT(A2,14),"-","")*1),LEN(SUBSTITUTE(RIGHT(A2,13),"-",""))=12) (
it validates this format:
C-###-#####-####.
But I have not been able to successfully adapt the above to what I need.
Any help would be appreciated.

Without thorough testing this formula appears to be working:
AND(ISNUMBER(LEFT(A1;3)*1);MID(A1;4;1)="-";ISNUMBER(MID(A1;4;3)*1);MID(A1;8;1)="-";ISNUMBER(RIGHT(A1;4)*1))

Per Erik

 

[MrAcquire]

Assuming that your phone number is in cell A2, enter the following in B2:

=AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMBER(VALUE(MID(A2,5,3))),ISNUMBER(VALUE(RIGHT(A2,4))),MID(A2,4,1)="-",MID(A2,8,1)="-")

BTW, I don't think your formula will validate correctly if the dashes are in
the wrong position.

 

[Theo]

Worked like a charm!!!
Thanks!
T

Assuming that your phone number is in cell A2, enter the following in B2:

=AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMBER(VALUE(MID(A2,5,3))),ISNUMBER(VALUE(RIGHT(A2,4))),MID(A2,4,1)="-",MID(A2,8,1)="-")

BTW, I don't think your formula will validate correctly if the dashes are in
the wrong position.

 

[Rick Rothstein \(MVP - VB\)]

Are you sure? Try entering this... 1e3-4e6-7e90

Rick

 

[Rick Rothstein \(MVP - VB\)]

I am pretty sure this formula is fool-proof...

=AND(LEN(A2)=12,SUMPRODUCT(--ISNUMBER(--MID(A2,ROW($1:$12),1)))=10,MID(A2,4,1)="-",MID(A2,8,1)="-")

Rick

 

[Rick Rothstein \(MVP - VB\)]

Or variations on this... -23--12--123

Rick

Are you sure? Try entering this... 1e3-4e6-7e90

Rick