Convert cells from TEXT into GENERAL in one step

[Flare]

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.

 

[Norie]

How are you initially getting the data into Excel?

 

[Flare]

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.

 

[Norie]

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?

 

[Flare]

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.

 

[Norie]

Did you try the Text to Columns suggestion in the link?

???

 

[Flare]

Hmmm... actually no... Let me try that out... could help

 

[Flare]

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!