phone number format

[Omar]

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

 

[Bob Phillips]

=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)

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

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