Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating tekst into numbers
Hi
I've importet a file and excel can't seem to understand that all the numbers are, just that, numbers. So every second collum reads the numbers as letters. (In the other colums it read numbers as numbers) I've tryed everything that is suggested on this page and in the help files, but still... Have done the substitute formula and even tho it takes away the space field it do not read the numbers as numbers, but as letters. Hopefully you understand my bad english, and any suggestions will be tryed cause I really have to fix this problem. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating tekst into numbers
================================================== ===============
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 "Gilette" wrote in message ... | Hi | I've importet a file and excel can't seem to understand that all the numbers | are, just that, numbers. | So every second collum reads the numbers as letters. (In the other colums it | read numbers as numbers) | I've tryed everything that is suggested on this page and in the help files, | but still... | Have done the substitute formula and even tho it takes away the space field | it do not read the numbers as numbers, but as letters. | | Hopefully you understand my bad english, and any suggestions will be tryed | cause I really have to fix this problem. | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formating tekst into numbers
If you have spaces in there, then Excel will treat the values as text.
SUBSTITUTE can get rid of the spaces, but will still return a text value. All you need to do is multiply the SUBSTITUTE formula by 1 (or add zero) to convert the answer to a number, i.e.: =SUBSTITUTE( ... )*1 Hope this helps. Pete On Dec 6, 10:56 am, Gilette wrote: Hi I've importet a file and excel can't seem to understand that all the numbers are, just that, numbers. So every second collum reads the numbers as letters. (In the other colums it read numbers as numbers) I've tryed everything that is suggested on this page and in the help files, but still... Have done the substitute formula and even tho it takes away the space field it do not read the numbers as numbers, but as letters. Hopefully you understand my bad english, and any suggestions will be tryed cause I really have to fix this problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formating numbers | Excel Discussion (Misc queries) | |||
formating numbers | Excel Discussion (Misc queries) | |||
formating numbers | Excel Discussion (Misc queries) | |||
formating numbers | Excel Discussion (Misc queries) | |||
formating numbers | Excel Discussion (Misc queries) |