| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
phone number format
Dear Experts,
I have one column with 4424 rows represents customer's phone numbers as follow. 971-4-5735403 How can I know if the last part (5735403) contains seven digits only? Many thanks in advance Omar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
phone number format
=IF(AND(LEFT(RIGHT(A1,7),1)<"-",ISNUMBER(--RIGHT(A1,7))),"OK","Problem")
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Omar" wrote in message ... Dear Experts, I have one column with 4424 rows represents customer's phone numbers as follow. 971-4-5735403 How can I know if the last part (5735403) contains seven digits only? Many thanks in advance Omar |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
phone number format
Omar,
If the format is consistent with 2 "-" as delimeters then this formula =IF(LEN(MID(A1,FIND("-",A1,FIND("-",A1,1)+1)+1,999))=7,"Correct Length","Incorrect") dragged down will tell you. Mike "Omar" wrote: Dear Experts, I have one column with 4424 rows represents customer's phone numbers as follow. 971-4-5735403 How can I know if the last part (5735403) contains seven digits only? Many thanks in advance Omar |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
phone number format
Put this in the first row of an unused column and copy down as far as you
like... =IF(A1="","",IF(ISNUMBER(--RIGHT(SUBSTITUTE(A1,"-","x"),7)),IF(ISNUMBER(--RIGHT(SUBSTITUTE(A1,"-","x"),8)),"No","Yes"),"No")) Rick "Omar" wrote in message ... Dear Experts, I have one column with 4424 rows represents customer's phone numbers as follow. 971-4-5735403 How can I know if the last part (5735403) contains seven digits only? Many thanks in advance Omar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing Phone number format | Excel Worksheet Functions | |||
| Format change of phone number | Excel Discussion (Misc queries) | |||
| How to format a phone number in Excel | Excel Discussion (Misc queries) | |||
| Join three columns containing phone number and format as (000) 000 | Excel Discussion (Misc queries) | |||
| how do I add phone number format as a permanent custom format? | Excel Discussion (Misc queries) | |||
Linear Mode
