Excel not recognizing numbers

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?

November 25th, 2014 11:03pm

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?

Free Windows Admin Tool Kit Click here and download it now
November 25th, 2014 11:17pm

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
November 26th, 2014 7:48am

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

Free Windows Admin Tool Kit Click here and download it now
November 26th, 2014 12:25pm

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

November 27th, 2014 6:02am

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
Free Windows Admin Tool Kit Click here and download it now
November 27th, 2014 9:21am

Great and thanks!

December 2nd, 2014 11:39pm

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.
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2015 1:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics