Cell Formatting changes

[Randy]

I have a column of numbers that need to be in five digit
format. I used the cell fomatting and custom, by placing
five zeros (00000)in the "type" field. When I look at the
formating for any number the Excel added zeros to to make
it five digits, the cell is formatted as "Special Zip
Code". The numbers that were already five digit the
formatting stays the same (custom). Is there a fix or a
work around for this? Apparently this is causing some
problems with lookup functions.

Thanks
Randy

 

[Dave Peterson]

Claire, is this you???

If no, then Claire asked a very similar question today:

I never noticed this before, but it looks like excel doesn't care about why you
formatted your range to have up to 5 leading 0's.

00000 is the same format as the 5 digit zipcode.

On the other hand, VBA doesn't call the format Zip. It uses 00000.

Format a cell (A1) with for a 5 digit zip and another cell (A2) as custom 00000.

then hit alt-F11 (to get to the VBE) and then hit ctrl-G (to see the immediate
window):

Type this in and hit enter:

?range("a1").numberformat

Then
?range("a2").numberformat

You'll see "00000" returned for both.

I got:

?range("a1").NumberFormat
00000
?range("a2").NumberFormat
00000

So I don't think it's anything you have to worry about.


==========
Randy, I'm not sure why it would have any affect on your =vlookup() function.

Any chance that you have some cells entered as Text and some as numbers--but
both look the same from the worksheet view?

 

[Randy]

Dave, no this isn't Claire
The output looks fine, but I beleive that the problem is
this data is being imported into another program, and for
some reason it's choking on the cell format. I'm not
involved in that portion of the project, so I can't say
for certain. All I know is I was asked to "fix it".
I know the VB code is okay, so I guess that I need to
clarify exactly what the issue is at the other end is
before I go any further.
Thanks for your help, Dave.

Cheers

Randy

 

[Dave Peterson]

Apologies to both you and Claire <bg>.

If you still have problems, then you may want to post a few more
particulars--like how the data is being exported.