Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
We receive XLS files which contain columns of numbers which we then use to calculate other fields. The columns should just contain numbers but some (randomly) seem to have been created as numbers in text format (i.e. it shows the number zero). If you try and test for them being zero it fails e.g. A1=0 (or what looks like zero!) =if(a1=0,true,false) gives false if you re-type over A1 with a zero number on the keyboard it works. Excel flags the original data as being held in character format. Using FORMAT CELL to change the zero to GENERAL or NUMBER has no effect on the test failing. How could the zero number have been created as character format and whats the best way to prevent it? Is there any way to automatically convert any rows in this state to true numbers? We are using Excel 2003 SP2 -- 525047 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Hi 525047,
Try this put 1 into a blank cell then copy it, then select all your data and choose paste specialcheck multiply option and OK. That will convert anything that is text to a number. HTH Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Format all to General.
Copy an empty cell and select your range of numbers. EditPaste SpecialAddOKEsc. Should be good to go. Prevention would be at the sending end of the cycle I suppose. Make sure all is numeric before you receive the files. Gord Dibben MS Excel MVP On Wed, 16 Aug 2006 00:44:59 +0100, 525047 wrote: We receive XLS files which contain columns of numbers which we then use to calculate other fields. The columns should just contain numbers but some (randomly) seem to have been created as numbers in text format (i.e. it shows the number zero). If you try and test for them being zero it fails e.g. A1=0 (or what looks like zero!) =if(a1=0,true,false) gives false if you re-type over A1 with a zero number on the keyboard it works. Excel flags the original data as being held in character format. Using FORMAT CELL to change the zero to GENERAL or NUMBER has no effect on the test failing. How could the zero number have been created as character format and whats the best way to prevent it? Is there any way to automatically convert any rows in this state to true numbers? We are using Excel 2003 SP2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Hi Gord,
Just for interest sake. Is there any reason for you using add 0 rather than multiply by 1? Or is it just a toss of the coin either either type thing? Not being picky just interested to know if there is some situations where one way would work better than the other. Regards Martin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
I like copying an empty cell so that I can select a range that includes empty
cells. Watch the difference when you multiply by 1 or add 0 (not just an empty cell). MartinW wrote: Hi Gord, Just for interest sake. Is there any reason for you using add 0 rather than multiply by 1? Or is it just a toss of the coin either either type thing? Not being picky just interested to know if there is some situations where one way would work better than the other. Regards Martin -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Thanks Dave,
I see your point. Add is the better option for sure. I hope the OP is happy I seem to have hijacked his thread! Regards Martin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Martin
Dave has explained his and my reason for the empty cell and "add". Also, you don't have to go and clear contents on the cell after the paste. On the other hand.................. I think it was RagDyer who suggested that a seemingly empty cell could contain a space or two and screw up the operation so that is something to be wary of. I have been fortunate enough to never encounter that problem. Gord Dibben MS Excel MVP On Wed, 16 Aug 2006 12:22:06 +1000, "MartinW" wrote: Hi Gord, Just for interest sake. Is there any reason for you using add 0 rather than multiply by 1? Or is it just a toss of the coin either either type thing? Not being picky just interested to know if there is some situations where one way would work better than the other. Regards Martin |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Adding the empty cell is my method of choice--as opposed to adding a cell with
0. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Yes Dave,
You are dealing with semantics here. When I said add 0 that is the effect that copying a blank cell has. Regards Martin |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text prevent calculations
Yep. But adding 0 (a cell with 0) to an empty cell is not the same.
And yes, it is a minor point--but I thought it important to make the distinction. MartinW wrote: Yes Dave, You are dealing with semantics here. When I said add 0 that is the effect that copying a blank cell has. Regards Martin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting text to negative numbers! | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) |