Forcing formatting as number

oliviar

Board Regular
Joined
Sep 12, 2010
Messages
184
Hi Guys,
I have a basic formula in one of my columns in a query. Its saying 'if there was an order on this date, put the order number in this column'. Easy peasy.
Except the order numbers are being pulled from excel via a linked table.
And they INSIST of being outputted back to excel as text.
I have tried going to properties in the query and formatting the column as a number. And in excel, they seem to show up as a number.
But once back in excel... 'number stored as text, convert to number?'

Its frustrating for the fact that the table is humongous. And converting text to number can take 15 MINUTES!

Is there a way to conclusively format the text as numbers? Maybe in SQL?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Is there any reason that these order "numbers" need to be in number format? If they represent an order designation, what difference does it make? Will you be doing calculations on these "numbers?"

Alan
 
Upvote 0
That is an excellent point. However some being numbers and some being text causes problems with search, lookup and counting functions.
They are supposed to be numbers, so that's what I need to get them to be. :confused:
 
Upvote 0
If that is the only reason, why not take the easy route and make them all text?

Alan
 
Upvote 0

Forum statistics

Threads
1,224,552
Messages
6,179,484
Members
452,917
Latest member
MrsMSalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top