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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
How are you initially getting the data into Excel?
 

Flare

New Member
Joined
Nov 23, 2005
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 23, 2005
Messages
7

ADVERTISEMENT

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.
 

Flare

New Member
Joined
Nov 23, 2005
Messages
7
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,435
Messages
5,572,085
Members
412,441
Latest member
kelethymos
Top