Formatting General to Number
 

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!

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.
--
Sincerely, Michael Colvin


"RJohnson701CTS" wrote:

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!

R,

This might be a workaround:

=SUMPRODUCT(A2:A20*1)
--
Earl Kiosterud
www.smokeylake.com

"RJohnson701CTS" wrote in message
...
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!

Thanks, Michael and Earl! Michael's fix worked. Have a good one!

"Michael" wrote:

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.
--
Sincerely, Michael Colvin


"RJohnson701CTS" wrote:

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!

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=388704

Your welcome. Good luck
--
Sincerely, Michael Colvin


"RJohnson701CTS" wrote:

Thanks, Michael and Earl! Michael's fix worked. Have a good one!

"Michael" wrote:

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.
--
Sincerely, Michael Colvin


"RJohnson701CTS" wrote:

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!

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

RJohnson701CTS wrote:

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!

Thanks, guys. This is all good stuff to know....

"Jerry W. Lewis" wrote:

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

RJohnson701CTS wrote:

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!