Error: The number in this cell is formatted as text or preceded by an apostrophe?

[ks100]

I am trying to run formulas on a huge spreadsheet, however they are not working because the cells I am referencing have this error:

"The number in this cell is formatted as text or preceded by an apostrophe"

As such, when I reference it in my formula it does not work.


How do I strip THE ENTIRE workbook of its formatting at once so that all formulas work on all cells? I tried formatting the column as "number" in bulk and it did not seem to work.

The spreadsheet is far too large to do by hand.

[Ace_XL]

Try this..

- In a separate cell type the number 1
- Copy this cell
- Select your data range
- Paste special -- values -- multiply -- Ok

[ks100]

Try this..

- In a separate cell type the number 1
- Copy this cell
- Select your data range
- Paste special -- values -- multiply -- Ok

Worked, thank you!

Would be interested in hearing of an easier/quicker way though.

[MrShorty]

The error is telling you that you have numbers that are being stored as text. Your problem suggests that your formulas are expecting needing these numbers to be stored as numbers. Two basic approaches:

Change how these numbers are brought into Excel or into these cells so that they are entered/calculated as values instead of text strings. Exactly how to do this will depend on how these numbers are coming in -- hand entered, imported from external source, result of Excel formula, entered from VBA or other macro. If you can make appropriate changes to how these values come into Excel, you may be able to get them entered as numbers rather than text, then your subsequent formulas will work just fine.

Change how Excel treats the values after being entered. If you cannot change how the numbers are brought into Excel, you need to help Excel convert the text to number. Ace_XL showed you one way. Sometimes, you could add a similar formula in a helper cell (=1*A1) and Excel may convert the text to a number. You might also be able to use the =VALUE() function -- either as part of your formulas or in helper cells to convert the text strings to numbers http://office.microsoft.com/en-us/ex...583.aspx?CTT=1

[ks100]

The error is telling you that you have numbers that are being stored as text. Your problem suggests that your formulas are expecting needing these numbers to be stored as numbers. Two basic approaches:

Change how these numbers are brought into Excel or into these cells so that they are entered/calculated as values instead of text strings. Exactly how to do this will depend on how these numbers are coming in -- hand entered, imported from external source, result of Excel formula, entered from VBA or other macro. If you can make appropriate changes to how these values come into Excel, you may be able to get them entered as numbers rather than text, then your subsequent formulas will work just fine.

Change how Excel treats the values after being entered. If you cannot change how the numbers are brought into Excel, you need to help Excel convert the text to number. Ace_XL showed you one way. Sometimes, you could add a similar formula in a helper cell (=1*A1) and Excel may convert the text to a number. You might also be able to use the =VALUE() function -- either as part of your formulas or in helper cells to convert the text strings to numbers http://office.microsoft.com/en-us/ex...583.aspx?CTT=1

The error is telling you that you have numbers that are being stored as text. Your problem suggests that your formulas are expecting needing these numbers to be stored as numbers. Two basic approaches:

Change how these numbers are brought into Excel or into these cells so that they are entered/calculated as values instead of text strings. Exactly how to do this will depend on how these numbers are coming in -- hand entered, imported from external source, result of Excel formula, entered from VBA or other macro. If you can make appropriate changes to how these values come into Excel, you may be able to get them entered as numbers rather than text, then your subsequent formulas will work just fine.

Change how Excel treats the values after being entered. If you cannot change how the numbers are brought into Excel, you need to help Excel convert the text to number. Ace_XL showed you one way. Sometimes, you could add a similar formula in a helper cell (=1*A1) and Excel may convert the text to a number. You might also be able to use the =VALUE() function -- either as part of your formulas or in helper cells to convert the text strings to numbers http://office.microsoft.com/en-us/ex...583.aspx?CTT=1

Can I just export as .CSV and then import back in to clear all formatting? I don't care about preserving anything as this is strictly a carbon copy for analysis of numbers only.

[MrShorty]

Sounds like one approach. Not sure that it sounds easier or quicker than Ace_XL's suggestion, but I expect it would work just fine.

[brunocinelli]

I'm facing the same problem and the solution proposed above (copying the number "1" from another cell and pasting special+multiplying the cells formatted as text) works perfectly when performed manually, but for some reason it doesn't work when performed through a macro.

I've recorded and tried using the following piece of code to perform this:

Please Login or Register  to view this content.

being "F2" the adress where I've typed "1" and "F7" the beginning of the column where the numbers are currently formatted as text.

Apparently, this part of the code is being simply ignored when I run the macro. Could anyone please advise on how I could solve this via macro?

tks in advance

[MrShorty]

brunocinelli: This forum can be pretty strict about not asking your own question in someone else's thread. Please start your own thread -- with a link to this thread if it will help people understand your question.

[Fayyaz Ahmed]

Dear i have made an userform for data entry. my problem is that the cell come with the error The number in this cell is formatted as text or preceded by an apostrophe?