I have a SQL database with Model, PartDescription, PartNumber, Qty, Price, Cost

When I run a query to export the data to Excel 2013 is does not recognize the output as numbers.

I have tried format cells-->number; I have tried coping and pasting values; I have tried every kind of trick I can think of...I have tried everything...even sticking the data into a little database and exporting it again and that doesn't even work...once it's in Excel, it's not a number, it's General and will not be changed.

The only thing that works is manually typing over the data in the cells...I can't do that for 34,444 rows of data.

Has anyone else run into this problem and is here a solution?

I got it to recognize as numbers, finally, by copying and pasting into Notepad, then copying and pasting that back into Excel.

What the heck is going on?
Any ideas?

Re:  convert text to numbers

Using paste special, you can multiple the data set by 1 (copy a cell with 1 in it) to convert data.
or
Using Text to Columns will also work, usually starting it up and clicking OK does the trick.
(a couple of my programs include a 'Clean' utility that uses the VBA CDbl function to do the job)
'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

Hi,

Does the suggestion provide by James fix this issue?

If not, I would like to move this thread to Excel IT Pro Discussions forum to get more effective response since it is more relative to end-user.

Regards & Fei

Hi,

Please try James's suggestion first. If this issue still exists, please upload a sample Excel file that included the data through OneDrive. I want to test it.

Regards,

George Zhao
TechNet Community Support

Once the data is on Excel, you can use "numbervalue" formula to convert the numbers that are otherwise considered as text by Excel. Once you apply the formula, drag the formula for all your 34K plus rows and you are done.

There is another situation I had faced, when you select the cell, press F2(edit mode for cell) and then hit enter, that converts text to numbers too, to achieve this you can write a simple macro to (.SendKey) is the method to send "F2" and then "Enter" key to those 34K plus cell and that should help fix the issue too. 

However I would recommend the former suggestion of mine as its just a formula and easier than the latter one.

Regards,

Sunil Thacker

• Marked as answer by GretaF Tuesday, December 02, 2014 8:39 PM

Great and thanks!

I've also discovered that you can insert a column, say column B and then =A1*1 into B1 and run that down, then use column B as column A.  Multiplying by 1 will convert to number.