formatting imported data

  • Thread starter Thread starter KEN
  • Start date Start date
K

KEN

When using the import data wizard in an Excel
spreadsheet, all data in the Wizard appears in the
correct format. Full amount shows the .00, any blank
cells show .00, any amount such as 123.10 show that way.
However, when Excel spreadsheet opens up the zeros are
gone. Blank cells show as 0, full amount show as 1234
and the amount 123.10 shows as 123.1
I can reformat the cells to be a number with 2 decimal
places but then I must change all columns in the work
sheet to be TEXT and then I lose the zeros again.
How can I keep the zeros? I am submitting the worksheet
to the state of PA for our PAUC2A data and the text
formatting is their requirement. Why are the zeros lost
once the spreadsheet initially opens?
 
When Excel imports numbers, it displays them in the format the spreadsheet
asks for, not the input format.

The default format is General, which is why Excel displays 123.10 as 123.1.

I'm amazed the state of PA wants a *worksheet* with the numbers as Text. How
would this be of any benefit to them? They would just have to convert them
to numbers to work with them.

If you are really sure this is what they want, the only solution I see is to
format your data using the TEXT function. For example, =TEXT(123.1,"0.00")
will give you a text cell with 123.10 in it.
 
Back
Top