Retaining number format

[Dawn]

I have a problem with some numbers in a report formatting incorrectly, am
hoping this can be fixed with VBA somehow.

I have a database, which shows the following number: 03275820.0
When the report is put into Excel, it comes out like this: 3275820

I am trying to find a way to keep the number exactly as it is entered.

If we enter an extra character into the database into the number field (for
instance: 03275820.0+ ), the number format is retained. So far so good.

I thought we could just do a find an replace to take out the extra
character, but no luck. If I do that, even if I've formatted the cell as
Text beforehand, Excel once again automatically applies the number format and
changes the number to read 3275820. (doh!)

If I apply the TEXT format to the column, and then manually go through the
report and find the extra character at the end of the numbers and manually
delete it and hit enter, Excel will then mercifully tag it as a number
formatted as text, and keep the proper format. This works, but isn't
terribly convenient. Was hoping this cro-magnon way of doing it could be
done by a macro somehow.

BTW, this column is a reference number, with thousands of matters and maybe
50 or 100 *different* correct number formats used in the column (e.g.,
NN/NNNN NNNN-NNNNN, NNNNNNN.N, NN/ABCNN/NNNNN, etc etc), so I can not set a
specific number format to the column and be done with it.

I can manage to record simple macros, but I'm no programmer. Would be very
appreciative of any suggestions.

 

[Allllen]

How about this

Use your extra character in the database or something stupid like 0123.4fish
Open the file in excel.
Turn the autofilter on.
Filter for contains "fish".
Select the column with this data.
Format cells as text (ie all the visible cells in the column).
Clear the filter.
Select the whole column with the data.
Replace "fish" by nothing.

I don't see why that wouldn't work.

 

[Dawn]

Unless you mean something other than using the find-and-replace function,
that's the scenario I detailed that I tried; and sadly it doesn't work. After
the find and replace is done, Excel *changes* the format of the cell back to
number format and then strips away the leading zero and the .0 ending. The
only way I can get it to keep the text format applied is by manually going
into each cell and deleting the characters. If I use find and replace it
screws it up.

 

[Patrick Molloy]

unfortunately 03275820 is text, not a number, but Excel "sees" this as a
number and thus strips off the offending preceding zero

maybe you could add a single quote mark
'03275820

so that excel sees text?

alternatively, format the column in the worksheet as TEXT before you put the
results in - I haven't tried this, but its an idea ...