exporting to excel

[camri6]

I exported some data from a program I use at work. the whole spreadsheet has
cells filled with percentages that I can't format, add together, etc.. I try
to add them together using the Sum function but it keeps kicking out 0.0%....
The sheet/workbook is not protected. Can anyone help

 

[FSt1]

hi
did your program you use at work export the data to excel as text?????
sounds like it. you cannot add or apply number formats to text.
test.
somewhere on the sheet (anywhere), enter a 1(one). copy it.
high light some data then paste special multiply.
if your data is numbers stored as text, that should remove the text format.

Regards
FSt1

 

[Gord Dibben]

Could be the data is in text format.

Format all to General then select a blank cell and copy it.

Select the range of data and Edit>Paste Special>Add>OK>Esc

Try your SUM again.


Gord Dibben MS Excel MVP

 

[Shane Devenshire]

Hi,

Most likely your data is text - see details below:

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.
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.

If this information is helpful, please click the Yes button.

Cheers,
Shane Devenshire