| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to Count Zip Code Digits With Leading Zero
Hello,
I have a zip code as 06110 formatted as zip code. I used LEN on that zip code and it returns a 4 because it's not reading the leading zero. Is there a function that will return a value of 5 for a zip code with a leading zero and still be able to return a value of 5 for zip codes that have 5 digits? Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Count Zip Code Digits With Leading Zero
You would need to enter the data as text. Numbers are stored without
leading zeroes. You could, of course, use =LEN(TEXT(A2,"00000")) That will return 5 for integers up to and including 5 digits, but will allow numbers longer than 5 digits to be counted as longer. -- David Biddulph "Daren" wrote in message ... Hello, I have a zip code as 06110 formatted as zip code. I used LEN on that zip code and it returns a 4 because it's not reading the leading zero. Is there a function that will return a value of 5 for a zip code with a leading zero and still be able to return a value of 5 for zip codes that have 5 digits? Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Count Zip Code Digits With Leading Zero
"Daren" wrote: Hello, I have a zip code as 06110 formatted as zip code. I used LEN on that zip code and it returns a 4 because it's not reading the leading zero. Is there a function that will return a value of 5 for a zip code with a leading zero and still be able to return a value of 5 for zip codes that have 5 digits? Thanks! Daren The problem with a zip code format is just that, it changes the way a number looks so len will ignore a leading zero and LEN(12.00) will always be 2. You can convert the number to text with a helper column and then copy and paste special the values over the formulas and then cut and past them into the zip codes. if your zip is in A6 use; =IF(LEFT(A6,1)="0",A6,"0"&A6) and copy down. Regards Peter Atherton |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to Count Zip Code Digits With Leading Zero
If you have the cell formatted as Zip Code, why are you checking its length
at all? Wouldn't all your zip codes have to be 5 characters long with that format? Or are you putting something else besides zip codes in a cell formatted as Zip Code? If that is what you are doing, then what other entries can there be? -- Rick (MVP - Excel) "Daren" wrote in message ... Hello, I have a zip code as 06110 formatted as zip code. I used LEN on that zip code and it returns a 4 because it's not reading the leading zero. Is there a function that will return a value of 5 for a zip code with a leading zero and still be able to return a value of 5 for zip codes that have 5 digits? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Truncate leading digits of large number | Excel Discussion (Misc queries) | |||
| zero fill leading digits | Excel Discussion (Misc queries) | |||
| Validation rule - custom 5 digits w/ leading zeroes | Excel Worksheet Functions | |||
| Leading digits | Excel Discussion (Misc queries) | |||
| how do you display a zip code with a leading zero | Excel Discussion (Misc queries) | |||
Hybrid Mode
