Leading zero's trail Number

C

C-Breeze

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
 
T

Teethless mama

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,SMALL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))

ctrl+shift+enter, not just enter
copy down as far as needed
 
T

Teethless mama

Sorry, wrong post


Teethless mama said:
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,SMALL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))

ctrl+shift+enter, not just enter
copy down as far as needed
 
F

FSt1

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top