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

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

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

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