I had a number of columns that contained formulas and i used the "Paste
Values" fuction to replace the formula. The problem is that the values are
not being treated like numbers. If I look at the values in the cells the
numbers appear with with the justification characters beside them. If I
manually remove the character or reinput the number it will then fuction as a
number. If i don't I cannot perform even simple calculations like SUM with
them. The results are incorrect. Is there any way to remove these characters
accross many cells at the same time? I've tried to change the formatting and
deselecting all justifications and that doesen't work either. Ive never seen
this before, a number that looks like a number but isn't a number.

================================================== ===============
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc.
In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!

Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it. EditCopy.
Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded he
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

================================================== ===============

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Picman" wrote in message ...
|I had a number of columns that contained formulas and i used the "Paste
| Values" fuction to replace the formula. The problem is that the values are
| not being treated like numbers. If I look at the values in the cells the
| numbers appear with with the justification characters beside them. If I
| manually remove the character or reinput the number it will then fuction as a
| number. If i don't I cannot perform even simple calculations like SUM with
| them. The results are incorrect. Is there any way to remove these characters
| accross many cells at the same time? I've tried to change the formatting and
| deselecting all justifications and that doesen't work either. Ive never seen
| this before, a number that looks like a number but isn't a number.

How do i avoid this issue in the future?

"Niek Otten" wrote:

================================================== ===============
Your Numbers don't behave (like numbers)
Niek Otten, May 11, 2006

Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs, etc.
In short:

Your Numbers look like Numbers, but they really are Text.
Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!

Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use Excel's
ISNUMBER() function to check your cells; maybe you solved your problem in the first step!

· Format an empty cell as Number. Enter the number 1 in it. EditCopy.
Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now
· If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the number
of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
function to remove them
· If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN() function
to remove most of them
· If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
David McRitchie's TRIMALL() function to remove them. It can be downloaded he
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

================================================== ===============

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Picman" wrote in message ...
|I had a number of columns that contained formulas and i used the "Paste
| Values" fuction to replace the formula. The problem is that the values are
| not being treated like numbers. If I look at the values in the cells the
| numbers appear with with the justification characters beside them. If I
| manually remove the character or reinput the number it will then fuction as a
| number. If i don't I cannot perform even simple calculations like SUM with
| them. The results are incorrect. Is there any way to remove these characters
| accross many cells at the same time? I've tried to change the formatting and
| deselecting all justifications and that doesen't work either. Ive never seen
| this before, a number that looks like a number but isn't a number.

If you paste to an area which has not been used before, it shouldn't be a problem.
If it has been used before, Select the area and format as General.
Pasting from websites or from other applications often requires additional work, as described, afterwards.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Picman" wrote in message ...
| How do i avoid this issue in the future?
|
| "Niek Otten" wrote:
|
| ================================================== ===============
| Your Numbers don't behave (like numbers)
| Niek Otten, May 11, 2006
|
| Your numbers sort incorrectly, are not included in SUMs, cause #VALUE! results in formulas, cannot be found in LOOKUPs,
etc.
| In short:
|
| Your Numbers look like Numbers, but they really are Text.
| Sure! You formatted them as numbers, but alas, formatting afterwards doesn't help. Believe me, they are Text!
|
| Here's a checklist which will help you solve most known cases. Make a copy of your workbook before trying! Always use
Excel's
| ISNUMBER() function to check your cells; maybe you solved your problem in the first step!
|
| · Format an empty cell as Number. Enter the number 1 in it. EditCopy.
| Select your "numbers". EditPaste Special, check Multiply. Hopefully your cells are "real" Numbers now
| · If that doesn't help, there may be spaces in your "numbers". You can use the LEN() function to compare the
number
| of characters that Excel sees in the cell with the number of characters you see. If you suspect spaces, use Excel's TRIM()
| function to remove them
| · If that doesn't help, there may be nonprintable characters in your "numbers". You can use Excel's CLEAN()
function
| to remove most of them
| · If that doesn't help, there may be non-breaking spaces in your "numbers" (mostly acquired from Web Pages). Use
| David McRitchie's TRIMALL() function to remove them. It can be downloaded he
| http://www.mvps.org/dmcritchie/excel/join.htm#trimall
|
| ================================================== ===============
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "Picman" wrote in message ...
| |I had a number of columns that contained formulas and i used the "Paste
| | Values" fuction to replace the formula. The problem is that the values are
| | not being treated like numbers. If I look at the values in the cells the
| | numbers appear with with the justification characters beside them. If I
| | manually remove the character or reinput the number it will then fuction as a
| | number. If i don't I cannot perform even simple calculations like SUM with
| | them. The results are incorrect. Is there any way to remove these characters
| | accross many cells at the same time? I've tried to change the formatting and
| | deselecting all justifications and that doesen't work either. Ive never seen
| | this before, a number that looks like a number but isn't a number.
|
|
|