Excel 2003: numbers copied from website are treated as text

[Larry Linson]

I copied several columns of data (Verizon wireless detail data), and one
column (minutes) is treated as text. I want to use the values in a
calculation (summation), but that gives a data type error.

The Help on converting numbers stored as text advises:

1. On the menu: Tools | Options | Error Checking
2. Make sure "Enable background error checking" and "Number stored as text"
both Selected, click OK
3.On the worksheet, select any single cell or range of adjacent cells that
has an error indicator in the upper left-hand corner

*** no cells have an error indicator ***
*** but the numbers are left-justified ***
*** which indicates text to me ***

4. Next to the selected cell or range, click the error button then click
Convert to Number

*** since no cells have error indicator,
cannot get the error button to click

How can I convert that range, or column, to numeric from the numeric text --
there is no non-numeric data in the range; it is all integer numbers.

Thanks,
Larry

 

[T. Valko]

If it's copied from a website there may be unseen whitespace characters like
spaces. Sometimes this will take care of it:

Select the range of cells in question
Goto the menu Data>Text to Columns
Just click Finish

 

[Pete_UK]

Often you will get the non-breaking space character (code 160) in
cells that you download. Highlight the offending column, then CTRL-H
(Find & Replace):

Find what: Alt-0160
Replace with: leave blank
Click Replace All

where Alt-0160 means to hold down the Alt key while typing 0160 on the
numeric keypad.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

It would help if you showed us an example of the way the data has come over.

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being entered
as text. Numbers can be stored as text by 1. preformatting the cell to Text
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
text number, 4. Because you used the Text to Columns command and converted it
to text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles may
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers. (this is not available in 2000 or earlier)
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.