Number Formatting
Posted by Nick Laul on February 02, 2001 8:44 AM
I have a spreadsheet where all cells are formatted as text. The spreadsheet has 0's and 1's and I want to sum the columns. I changed the formatting of the 0's and 1's to a number, but when I do a sum, the sum shows up as zero. If I change format to number, then reenter the data, it sums ok. But I dont want to re-enter the whole data. Please help.
Posted by Dave Hawley on February 02, 2001 9:05 AM
Hi Nick
Lets assume your text numbers are in A1:A10
In A11 put:
=IF(A1="","",VALUE(A1))
Copy this down to A21.
Now highlight A11:A21 and copy then Edit>PasteSpecial/Values over the top.
Copy A11:A21 and pastespecial over A1:A10.
Hope this helps
Dave
OzGrid Business Applications
Posted by Faster on February 02, 2001 9:40 AM
When you change a cell's format from text to number
it usually needs to be edited before the change takes
effect. This codes multiplies the cells by 1. I hope
it helps.
Sub EditCell()
Do While Selection <> ""
Selection = Selection * 1
Selection.Offset(1, 0).Select
Loop
End Sub
Posted by Mark W. on February 02, 2001 10:03 AM
An ever easier way...
Nick, once you've changed the format all you really
have to do is perform an Edit Replace (Ctrl+H) changing
0 to 0 and 1 to 1.
