Excel 2007: cannot convert numbers stored as text to number format

[rschackner]

There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:
"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.

The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries. Any advice?

I think I included everything I know at this point, let me know if you need more info. Thanks.

 

[rschackner]

Alright I think I've gotten it. When I selected the first cell in the column and clicked above in the formula bar there was a space between the blinknig cursor and the number. I hit backspace then enter and the number lined up on the right side of the cell where it should be. Now,how would I get the space out of all the cells? I tried the CLEAN function and it did not help.

 

[eog2007]

Alright I think I've gotten it. When I selected the first cell in the column and clicked above in the formula bar there was a space between the blinknig cursor and the number. I hit backspace then enter and the number lined up on the right side of the cell where it should be. Now,how would I get the space out of all the cells? I tried the CLEAN function and it did not help.

try the =trim( ) function, it deletes all spaces at beginning or end of a string of numbers/text

best way to do this is to go to a spare column and then type in =trim( ) and point it to the cell containing the number you want to clean, then copy it down the length of the data

then try your sum and it should work fine

 

[Excelestial]

TRIM(A1)...Run it down all your rows.

 

[rschackner]

=TRIM() and =CLEAN() functions still would not remove the space after the number in each cell, but I found a way around it. I highlighted the entire column, copy and pasted it in to Word, and did a Replace. In the "Find what:" field, I entered Special-Nonbreaking Space and left the "Replace with:" field blank. I then copy and pasted the data back into the spreadsheet and the data came in as numbers and I could perform the =SUM function.

Thanks for all of your help everyone, I will definately be back up here frequently with excel issues since I use it everyday. I have no idea what the issue is with this particular sheet but this way will work for now.

 

[VoG]

That sounds like you had the dreaded CHAR(160). The TrimAll macro will remove those: select the range then run the macro. http://www.mvps.org/dmcritchie/excel/join.htm

 

[Colin Legg]

There are a few unicode nonprinting characters that CLEAN() will not remove, specifically values 127,129,141,143,144 and 157. This list is in the helpfile.

If you determine which non-printing character(s) you have in the cells (to avoid a formula nesting for all six) then you can remove them with the SUBSTITUTE() worksheetfunction which will mean that you don't have to export to MS Word.

 

[rschackner]

It was CHAR(160). I did a =SUBSTITUTE(F1,CHAR(160),) function on the first cell in the column, auto filled the =SUBSTITUTE function down the entire column, copy-paste special-values into another column next to the original data, and the cell format to number and it worked.

Thanks again for all of your help and the quickness of responses.

 

[Anilesh]

I am trying to enter number 456789 in excell worksheet 2007 automatic changes to 456780 last digit changs to 0 only and i tried to change cell format to number, general, text etc. but no change, is anybody know how to solve this?