Formatting General to Number

R

RJohnson701CTS

I'm trying to replace text with number values via Find and Replace but when I
try to sum the number values, nothing adds up. I've tried to format the
cells to Number from General, but Excel doesn't seem to want to do it. All
the numbers I've put in using Find and Replace just stay in General format.
If I manually type the number in, they sum up fine. But I've got way too
much text to do it all by hand. Any ideas? Thanks!
 
M

Michael

Put 1 in a vacant cell, , copy the 1 and then select your text values, go to
edit - paste special and multiply. This will covert all to numbers.
 
D

dominicb

Good afternoon RJohnson701CTS

Try using this VBA code to do what you need.

Sub FixNum()
For Each UsrCell In Selection
UsrCell.FormulaLocal = UsrCell.FormulaLocal
Next
End Sub

Copy it into a blank module, highlight the problematic numbers and then
call the macro. This will convert all the "text numbers" to actual
values.

HTH

DominicB
 
J

Jerry W. Lewis

You alrady have 2 solutions, here is an explanation.

Formatting only changes the display, not the contents of the cell. In
particular, formatting cannot change text (even text digits) into a
number. You must either re-enter the contents after formatting the cell
to receive a number, or get Excel to coerce the contents into a number
(by performing math with +-*/ on the cell contents).

Jerry
 
Top