Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nine digits
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
nine digits
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 --- "Omar" wrote: Dear Experts, I have one column represents customers 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
nine digits
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 On Jan 28, 1:02*pm, Omar wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
nine digits
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 -- David Biddulph "Max" wrote in message ... 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 --- "Omar" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to customize number to 10 digits including 2 digits after deci | Excel Worksheet Functions | |||
last 2 digits | Excel Discussion (Misc queries) | |||
2 DIGITS OUT OF 4 | Excel Discussion (Misc queries) | |||
2 DIGITS OUT OF 4 | Excel Discussion (Misc queries) | |||
15 digits??? | Excel Discussion (Misc queries) |