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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,323
Latest member
Regash

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
Top