Exporting data to Excel

[Peledon]

How come when I export data to excel and reformat a column to numbers, it
doesn't recognise them as numbers and I have to retype the numbers in a
separate column to calculate them.
HELP PLEASE.

 

[William Horton]

Yes, Excel does do that. I'm not sure why but you do have to re-enter them.
HOWEVER, a shortcut would be to type a 1 in an empty cell. Then highlight
that cell and choose Edit / Copy from the menu path. Then highlight all the
cells that you would like to turn into numbers. Then choose Edit / Paste
Special from the menu path. Then choose the operation Multiply. All your
data should now be converted to numbers. You can delete the 1 that you typed
in the empty cell.

Hope that helps.

Bill Horton

 

[David Biddulph]

You haven't said what application you have used to export the data, nor have
you said in what format the data is exported, nor by what means you have
imported the data to Excel. If you look carefully at the data, either in
Excel or in something like Notepad, you might see the problem. One of the
most frequent problems is the introduction of extraneous spaces or
non-printable characters, so I would look for those first. You'll need to
sort out what the problem is before you can cure it. If you've put the data
in as text, merely changing the cell format to number won't change the
content of the cell. A format change merely changes how it's displayed.
Sometimes you can get away with using Edit/ Paste Special to multiply by 1
or to add zero.

 

[Peledon]

I thought that was a great idea but unfortunately I wanted to calculate
variance that have negative numbers. Calculating for instance 1 x -1.04
results =R11*X11.

 

[Peledon]

Thanks David. I am not entirely sure what the application from which I
export the data from it was designed specifically for our use, probably
oracle based.

I managed to get what I wanted by copy pasting special then Add, seemed to
do the trick.

Thanks so much for your help.

 

[Thomas]

Try http://www.sqlscripter.com to export data to Excel.

 

[Jason]

Can you give us more details. What are you exporting from? Access?
Also, if you have a column of numbers that excel has stored as text you
can tell this from the fact that text aligns to the left of the cell,
and numbers align to the right of the cell.

A quick way to convert a column of numbers stored as text into number
format is:

1) Type the number 1 in an empty cell
2) Select the cell and COPY
3) Select the complete column of numbers stored as text
4) Right click and select 'Paste Special'
5) Select 'Multiply' (from the 'operation' section of paste values
6) Click OK

This will convert every value in the column to actual number format,
which you can tell by the fact that the values will now be aligned to
the right.

(You can obviously then delete the 1)

Jay
__