Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cells formated as numbers are calculating like text
I have a workbook with several worksheets imported from a database. When I
check the format of the cells, they show up as Number with 2 decimal places. BUT when I try to sum the values in a column it sums to zero (the values are non-zero). If I try to add two cells in the range I get a #VALUE error. Also, when I try to use the sum function from the tool bar, it does not automatically populate any range. It's acting like the cells are text, but the format says that they are text. When I try top convert them to numbers: 1) there is no ! area that says they are text 2) when I use the "paste-special-multiply" to try to convert text to numbers, I still get 0 as a result ANY IDEAS/SUGGESTIONS? |
#2
|
|||
|
|||
Sometimes this works..........
Data TextToColumns Delimited Uncheck ALL Delimiters Next Insure General format is selected Finish Vaya con Dios, Chuck, CABGx3 "MM_BAM" wrote in message ... I have a workbook with several worksheets imported from a database. When I check the format of the cells, they show up as Number with 2 decimal places. BUT when I try to sum the values in a column it sums to zero (the values are non-zero). If I try to add two cells in the range I get a #VALUE error. Also, when I try to use the sum function from the tool bar, it does not automatically populate any range. It's acting like the cells are text, but the format says that they are text. When I try top convert them to numbers: 1) there is no ! area that says they are text 2) when I use the "paste-special-multiply" to try to convert text to numbers, I still get 0 as a result ANY IDEAS/SUGGESTIONS? |
#3
|
|||
|
|||
Check to see if "numbers" have what appears to be a leading or trailing
space. If yes, then edit the cell, paint through the "space" and press Ctrl+C to copy, then press Escape Next: EditReplace Find What: Click this field and press Ctrl+V (to paste in whatever the space character is) Replace with: Leave this blank (or delete it's contents) Then click Replace (to make sure it's doing the right thing) Then click Replace All Does that solve your problem? Ron |
#4
|
|||
|
|||
You might want to try something like this in a neighboring column:
=trim(a1)*1 ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "MM_BAM" wrote in message ... I have a workbook with several worksheets imported from a database. When I check the format of the cells, they show up as Number with 2 decimal places. BUT when I try to sum the values in a column it sums to zero (the values are non-zero). If I try to add two cells in the range I get a #VALUE error. Also, when I try to use the sum function from the tool bar, it does not automatically populate any range. It's acting like the cells are text, but the format says that they are text. When I try top convert them to numbers: 1) there is no ! area that says they are text 2) when I use the "paste-special-multiply" to try to convert text to numbers, I still get 0 as a result ANY IDEAS/SUGGESTIONS? |
#5
|
|||
|
|||
On Wed, 6 Jul 2005 17:04:03 -0700, MM_BAM
wrote: I have a workbook with several worksheets imported from a database. When I check the format of the cells, they show up as Number with 2 decimal places. BUT when I try to sum the values in a column it sums to zero (the values are non-zero). If I try to add two cells in the range I get a #VALUE error. Also, when I try to use the sum function from the tool bar, it does not automatically populate any range. It's acting like the cells are text, but the format says that they are text. When I try top convert them to numbers: 1) there is no ! area that says they are text 2) when I use the "paste-special-multiply" to try to convert text to numbers, I still get 0 as a result ANY IDEAS/SUGGESTIONS? The format of a cell does not directly give you information as to whether the contents are TEXT or NUMBER. One way to detect that is with the ISTEXT(cell_ref) function. If the data is coming via the WEB, the most common cause for the behavior you describe is the addition of a non-break space at the end of the number. This is CHAR(160). To "clean up" the data, you could try this formula: =--SUBSTITUTE(TRIM(A1),CHAR(160),"") If that doesn't do it, by using the MID function you can step through the contents of the cell one character at a time, and see exactly what you have. For example, with A1 containing the number 23, followed by the no-break space, do the following: B1: =MID($A$1,ROW(),1) C1: =CODE(MID($A$1,ROW(),1)) Select B1 & C1 and copy/drag down to row 4. You should see the following: 2 50 3 51 * 160 #VALUE! A similar strategy will let you analyze what's going on with your cells. HTH, --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
substracting numbers in non contigous cells | Excel Discussion (Misc queries) | |||
update row numbers after different active cells in macros followi. | Excel Discussion (Misc queries) | |||
Converting negative numbers in a range of cells to zero | Excel Discussion (Misc queries) | |||
Calculating without including Hidden Cells | Excel Discussion (Misc queries) | |||
combining several individual cells of numbers into one cell | Excel Worksheet Functions |