0

I'm using Excel 2010, and have a spreadsheet of simple numbers (they're 1-5 ratings for a range of questions), so I have questions across the top, and each person's responses as a separate row. The numbers are whole numbers, but each cell has an error checking marker saying 'Number Stored as Text'. So I highlighted all the cells, set cell format to 'Number' with 0 decimal places, and it doesn't do anything.

I'm quite competent with Excel, but am I missing something really stupid here?

The file is in Compatibility Mode, stored as .xls (through necessity I'm afraid)

  • Can you save the file to the current format so you aren't in compatibility mode? From what I understand, the whole number is input, not pulled from somewhere else. Is that correct? – CharlieRB Dec 11 '13 at 12:49
  • @CharlieRB I've just converted it to the current format, seems to do the same thing still. Yes it's an export from an SSRS report, so there are no formulae, just the values – laurencemadill Dec 11 '13 at 13:41
  • Do you have access to the SSRS report? (as in can you modify it or get it modified?) – CLockeWork Dec 11 '13 at 14:43
  • @CLockeWork yes, it's because of the datatype in the query and the way the responses are stored in the SQL database, there is a generic response table that creates a record for each response, no matter whether question is short text, long text, tickbox, numeric etc, so it has to be a txt field at that point. In the query I should probably have converted it to an int field where the question type calls for numeric response – laurencemadill Dec 11 '13 at 16:05
  • Alternatively you could convert it to a number at the report end with a CInt expression – CLockeWork Dec 11 '13 at 16:30
2

This often happens when the data is coming from somewhere else, and Excel hasn't read it as a number, so can't convert it to a number.

When you look at the error message there's an option to convert to number (in 2010 and up at least.) You can then select the entire column then press the error message, thereby changing all instances of numbers stored as text in the column to numbers.

The next simplest way to fix this is to add another column and add this formula to it:

=$A2*1

Where A is your ratings column. Or:

=[@[Rating]]*1

If you're using a Table (and have called your ratings column Rating)

  • Yes I can 'convert to number' one cell at a time, but I've got hundreds of responses each with multiple questions. I've converted to .xlsx as well, and still doesn't let me change it. – laurencemadill Dec 11 '13 at 13:35
  • forgot to say...your solution is helpful, it appears it may be the only way around it in this instance – laurencemadill Dec 11 '13 at 13:37
  • 1
    Sorry @laurencemadill, I should have been clearer about the ability to select them all and then use the Convert to Number function. Updated my answer – CLockeWork Dec 11 '13 at 14:40
  • thanks, I've removed my answer and accepted your explanation, alternative solutions are useful too – laurencemadill Dec 11 '13 at 16:08
0

The answer above didn't work for me because in addition to the cells that had numbers stored as text, I also had cells that were actual text and when I applied the formula to the actual text cells, it gave a #VALUE!.

As the exported file was 7000 rows and I have to run the report weekly, the other manual options weren't really feasible. I'm not the only one doing this report (and some of the others are not Excel savvy) so I needed something that would allow us to copy & paste the download as usual and automatically convert the numbers to number format but not mess up the text values either.

I came up with the formula below so my text options stay text and the numbers change to number format - so happy this works!

=IF(AND(A1="Yes"),"Yes",IF(AND(A1="Not applicable"),"Not applicable",IF(AND(A1="No"),"No",A1*1)))

Copied this down all the rows so it does it automatically for us now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.