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.

 

[VoG]

Hello and welcome to MrExcel.

Try selecting the cells, Data > Text to Columns and press Finish.

 

[pgc01]

Hi rschackner
Welcome to the board

Changing the format in this case changes nothing because excel does the calculations with the values, the formats don't matter.

If you cannot change the cells, because they are locked, you can convert them to number in the formula itself.

Ex.:

=SUMPRODUCT(VALUE(A2:A100))

 

[Colin Legg]

Hi and welcome to the forum.

In a spare cell type in the number 1. Copy it.
Select the range of cells containing the numbers stored as text.
Right click and paste special.
In the paste section choose Values.
In the operation section choose Multiply.
Tick the "Skip Blanks" checkbox.
Click on OK.
Clear out the spare cell.

 

[rschackner]

I just tried both the text to columns and the =SUMPRODUCT(VALUE(A2:A100)) approach and text to colums still gave me a "o" for a sum and =SUMPRODUCT(VALUE(A2:A100)) gave me a call with #Value.

Another thing. Whn I try to save the file I get this message:

"FaxDetailReport.xls may contain features that are not compatible with the Web Page. Do you want to keep the workbook in this format?

-To keep this format, which leaves out any incompatible features, click Yes.
-To preserve the features, click No. Then save a copy in the latest Excel format.
-To see what might be lost, click Help."

I don't know if this helps, but I am running out of ideas. I am way too stubborn to not figure this out, thanks for the help thus far.

 

[aberfroman]

The file not compatible with the Web Page sounds like a file extention attention message. I think you may be using Excel 2007 and are trying to save the 97-2003 extention and Excel is just warning you. Also, there may be some formulas that only work with 2007 on your sheet and since you are saving 97-2003 its just warning you. If you don't need to upload the file back to your server just save as xlsx.

 

[aberfroman]

As far as your original question I would try setting your fax log to a *.csv cause it sounds like there may be a formatting issue somewhere. Once you have opened the file in excel save it as *.xlsx and run your formulas and see if they work. If they do you may have to do some extra work with that sheet every time you need an updated report.

 

[Excelestial]

First use the text command to convert it into Excel's text language. If text string is in Cell A1, use...TEXT(A1,"###,###"). In an adjacent cell, multiply or divide this new string by 1. You have your number.

Of course, run these formulae down your page...be sure to reference the correct columns with the page numbers. Sum the column you used to multiply or divide by the number 1.

 

[pgc01]

... =SUMPRODUCT(VALUE(A2:A100)) gave me a call with #Value.

This means that you have some cells in the range that cannot be converted to numbers, which is not what I understood from your post.

In that case use:

=SUM(IF(ISNUMBER(VALUE(A2:A100)),VALUE(A2:A100)))

This is an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

and the formula skips cells with values that cannot be converted to number.

 

[Colin Legg]

The fact that PGC's original formula returned #VALUE! suggests to me that you could have non-printing characters hidden in the cell.

Does this work?
=SUMPRODUCT(--("0"&TRIM(CLEAN(A2:A100))))