Convert cells from TEXT into GENERAL in one step

Flare

New Member
Joined
Nov 23, 2005
Messages
7
Every time I transfer a file from an external database to Excel, I open it and the cells are formatted as “Text”, even if they really are numbers.

So what I have to do is create a new column, put in the function [e.g. “=value(A2)"]; copy it down so it affects all the rows; then I copy the contents of that column and paste it as values on top of the original one... finally ending up with a “General” values column.

Then I have to repeat this for all columns that are “Text” formatted!


Does anybody know how to avoid this? How can I convert the text-formatted cells into General in one single step; without having to do any of the bogus methods suggested by Microsoft in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;291047&sd=tech


Notice how the method I’m using is Number 4 in the article. Method 1 is nice but I have to do it for every single cell, can’t do it for a range of cells (at least I don’t think so). Method 2 is plain stupid and Method 3 is similar to 1, I have to do this for every single cell.

Thanks.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How are you initially getting the data into Excel?
 
Upvote 0
I work with SAP, so I use the function of sending data via the "Export to spreadsheet" option.

I have vendor numbers, document numbers and stuff. Excel interprets this kind of information as TEXT.
 
Upvote 0
Did you try the Text to Columns suggestion in the link?

I have vendor numbers, document numbers and stuff. Excel interprets this kind of information as TEXT.
How should Excel interpret the data?
 
Upvote 0
I want Excel to interpret them as either "General" or "Number" values.

If I highlight the range of cells (currently formatted as Text), and I select Format Cells; then choose "Number" with 0 decimals; after pressing OK, the cells continue as Text. I have to either retype the number to accept it as such, or use the "VALUE" formula in another cell.
 
Upvote 0
Wow... it worked!

I just had to select the range (or the whole column if I want), then use the "Text to columns" function, leave it in one cell but changing it to "General".

It basically rewrote all the fields as Numbers.

Amazing stuff... thanks a bunch for the tip!
 
Upvote 0

Forum statistics

Threads
1,216,172
Messages
6,129,291
Members
449,498
Latest member
Lee_ray

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