unable to "sum" cells in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

there's an "invisible" apostrophy or other character in front of each
number.....how do I get rid of these characters so that I can use the "sum"
function?
 
Try this:

Enter a 1 into any blank cell
Copy that cell
Select your range of numbers with the apostrophes
From the Edit Menu, choose "Paste Special"
Check the "Values" and "Multiply" options
Click OK
Delete the 1 that you entered originally

HTH,
Elkar
 
Elkar,

Thank you.

One more question - there's 2600 cells in the column, many are blank. Do I
need to highlight the column and format the cells to "number" instead of
"general?"
 
No, that wouldn't make any difference. The invisible apostrophes in a cell
forces Excel to ignore the cell format and treat the data as Text. If you
are entering data manually, leaving the format as General will be fine. If
you are importing from an external source, then the apostrophes will likely
still appear and override whichever format you've chosen.
 
If you have blank cells in the range to fix that should be kept blank, you can
select an empty cell
edit|copy
Select the range to fix
edit|paste special|check add.
 
Back
Top