Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zero's trail Number
We use Excel for our memebrship data, on our zip code entires for NJ and Y
which have a leading zero we get a blank. On last number in CC entry we only are able to display, 0, 1, or 2 -- How do we over come these issue? C-Ya GC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zero's trail Number
Assuming your data in A1:F5
NY MO IL NJ KS Jim x x x Joe x x x x Amy x x x Jane x x x x Criteria in G1 In H1: =LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$5,SMA LL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIREC T("1:"&ROWS($A$2:$A$5)))),ROWS($1:1))))) ctrl+shift+enter, not just enter copy down as far as needed "C-Breeze" wrote: We use Excel for our memebrship data, on our zip code entires for NJ and Y which have a leading zero we get a blank. On last number in CC entry we only are able to display, 0, 1, or 2 -- How do we over come these issue? C-Ya GC |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zero's trail Number
Sorry, wrong post
"Teethless mama" wrote: Assuming your data in A1:F5 NY MO IL NJ KS Jim x x x Joe x x x x Amy x x x Jane x x x x Criteria in G1 In H1: =LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$5,SMA LL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIREC T("1:"&ROWS($A$2:$A$5)))),ROWS($1:1))))) ctrl+shift+enter, not just enter copy down as far as needed "C-Breeze" wrote: We use Excel for our memebrship data, on our zip code entires for NJ and Y which have a leading zero we get a blank. On last number in CC entry we only are able to display, 0, 1, or 2 -- How do we over come these issue? C-Ya GC |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Leading zero's trail Number
hi
if you are entering the zips as numbers, excel will strip any leading zero off. to keep the leading zeros, you have to preformat the cell as text or preceed the entry with an apostrophe ie '00001. as excel see it leading zeros on numbers are unnecessary so it strips them but leading zeros on id numbers formated as text are kept. and a zip code would be considered an id number or a number(?) that you wouldn't want to do math to. think...have you ever had a reason to devide your zip by 2 or add 1000 to your phone number. technically these are not true numbers but ids using number characters as text. yes i know. real technical. but if you can't think like the computer, you have problems understanding what it's doing. regards FSt1 "C-Breeze" wrote: We use Excel for our memebrship data, on our zip code entires for NJ and Y which have a leading zero we get a blank. On last number in CC entry we only are able to display, 0, 1, or 2 -- How do we over come these issue? C-Ya GC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zero's in Excel | Excel Discussion (Misc queries) | |||
Leading Zero's | Excel Discussion (Misc queries) | |||
How to add leading zero's | Excel Worksheet Functions | |||
How to get the leading zero's added in a document | Excel Worksheet Functions | |||
Leading zero's | Excel Discussion (Misc queries) |