| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
validating telephone format
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
On Feb 11, 9:26 pm, Theo wrote:
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
Assuming that your phone number is in cell A2, enter the following in B2:
=AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMB ER(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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
Worked like a charm!!!
Thanks! T "MrAcquire" wrote: Assuming that your phone number is in cell A2, enter the following in B2: =AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMB ER(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" wrote: 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
Are you sure? Try entering this... 1e3-4e6-7e90
Rick "Theo" wrote in message ... Worked like a charm!!! Thanks! T "MrAcquire" wrote: Assuming that your phone number is in cell A2, enter the following in B2: =AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMB ER(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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
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 "Theo" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
validating telephone format
Or variations on this... -23--12--123
Rick "Rick Rothstein (MVP - VB)" wrote in message ... Are you sure? Try entering this... 1e3-4e6-7e90 Rick "Theo" wrote in message ... Worked like a charm!!! Thanks! T "MrAcquire" wrote: Assuming that your phone number is in cell A2, enter the following in B2: =AND(LEN(A2)=12,ISNUMBER(VALUE(LEFT(A2,3))),ISNUMB ER(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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| custom telephone number format | Excel Discussion (Misc queries) | |||
| Can I format telephone numbers? | Excel Worksheet Functions | |||
| Format Telephone Cell Range | Excel Worksheet Functions | |||
| how do i format telephone numbers | Excel Discussion (Misc queries) | |||
| Telephone number format | Excel Discussion (Misc queries) | |||
Linear Mode
