How to convert Excel imported numbers from text to numbers?

G

Guest

We have a software package from which we can export data to MS Excel.
However, when exported it arrives in Excel as text. Even though they are
numbers, they act like text. The only way to convert them text to numbers is
to edit the cell (press F2) and hit enter. I've tried every other method of
copying and pasting (even pasting special), but nothing seems to work. Any
ideas?
 
G

Guest

Depending on the version of Excel, you should get a
caution sign next to the cell when you highlight a number
stored as text.
When you put your cursor over the caution sign, it gives
you the option to convert the number stored as text to a
number.

If highlight a range that has a number stored as text at
the upper left, the convert will work for the whole range,
but you might have to scroll back to the upper left of the
range to see the caution sign
 
2

2rrs

Alden said:
We have a software package from which we can export data to MS Excel.
However, when exported it arrives in Excel as text. Even though they are
numbers, they act like text. The only way to convert them text to numbers is
to edit the cell (press F2) and hit enter. I've tried every other method of
copying and pasting (even pasting special), but nothing seems to work. Any
ideas?

Have a look here:
http://groups-beta.google.com/group...544f3a681cf/ca6d88671983a8e3#ca6d88671983a8e3
 
G

Guest

Simple, elegant solution. Thanks for the help! You've saved me from carpal
tunnel syndrome.

Alden
 
J

Jeff H

Alden said:
We have a software package from which we can export data to MS Excel.
However, when exported it arrives in Excel as text. Even though they are
numbers, they act like text. The only way to convert them text to numbers is
to edit the cell (press F2) and hit enter. I've tried every other method of
copying and pasting (even pasting special), but nothing seems to work. Any
ideas?

I use ASAP Utilities add in. One of its many features will change text
numbers to numbers in a range (or do the reverse). Go here...
http://www.asaputilities.com/
 
G

Guest

Hi Peo, (pls excuse me.. ) I dont understand your "import"?? ... the rest of
your solution is more or less the same as "Help" offers (add instead of
multiply etc.), but it doesn't work for me... and I won't use an add'in.. as
I don't know how that will work. I do mail you directly too..
Bregards Jonna
 
G

Gord Dibben

jonna

Try this(same as Peo's but with more detail).

Assume your range of cells that contain the exported data is A1:A10

Format these cells as General.

Format an empty cell as General.

Edit>Copy that empty cell.

Select A1:A10.

Edit>Paste Special(in place)>Add>OK>Esc.


Gord Dibben Excel MVP
 
G

Guest

thanks, but the real problem was that the "numbers" had an empty space in
front. I did find out and have used functions "Left" and "Right" .. with
sucess...
br
Jonna
 
G

Gord Dibben

Thanks for the feedback Jonna.

That was my next suggestion, but no need now.

But, I am surprised that a simple F2 and ENTER would work if there are spaces.


Gord
 

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