nine digits

[Omar]

Dear Experts,
I have one column represents customer’s phone numbers in the following
format 045735403, 04 is the key and the rest is the line number.
How can I know if the number contains nine digits only, not more not
less?
Many thanks in advance
Omar

 

[Max]

In B1, copied down: =LEN(A1)
will reveal the actual number of underlying characters that's in col A,
regardless of the formatting applied
(formatting affects only the display, not the underlying values)

You can apply it directly as a test for 9 char,
eg in C1, copied down: =LEN(A1)=9
will return it as TRUEs/FALSEs
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[muddan madhu]

try this

in adjacent cell of phone number put this formula
=IF(LEN(A1)<>9,"less or more than 9 digit","")

or

use conditional format
select the range where u have phone number then
go to | format | conditonal format | condition1 : formula is =LEN(A1)
<>9 | format | choose a color | ok | ok

 

[David Biddulph]

And remember that you would need to enter the data as a text string to get
9.

If it goes in as a number the length will be 8, even if displayed as 9
digits with a format 000000000