Ignore multiple "number stored as text" errors at once

I have a spreadsheet where a certain column has a lot of data which generates "number stored as text" errors. I want the numbers to be stored as text in this column for certain formatting purposes. Is there a way for me to quickly dismiss all these errors at once, or tell Excel to ignore this error for the entire row as a rule, without entirely disabling the error for the whole sheet or program?

microsoft-excel-2010

Iszi

Posted 2012-12-31T02:16:46.997

Reputation: 11 686

There is no way to deactivate error checking only for a certain column. You have to deactive number stored as column in general or to ignore the error indicator. – nixda – 2012-12-31T06:21:48.377

there is a way to de-activate check in cell, and this is persisted in XLSX but not in XLS though – Arioch 'The – 2013-03-05T12:28:27.320

Answers

Select the top-left first cell in the sheet that has the green triangle indicating the error
Scroll to the last bottom-right cell that has the error. Hold Shift and select that last cell
Go back to that first cell, there will be a clickable icon to do something about the error
Click on it and then click "Ignore Error"

This will ignore all the errors in the selection you have. But you must start with the first error to get the pop-up to ignore them.

Joseph

Posted 2012-12-31T02:16:46.997

Reputation: 738

7another tip, while in that cell, you can press Ctrl+Shift+Down arrow, then you will select all sells until the last cell with a value :) – chrispepper1989 – 2015-10-13T15:00:31.730

Uncheck this option:

File > Options > Formulas > Error Checking Rules > Numbers formatted as text or preceded by an apostrophe

dbsdba

Posted 2012-12-31T02:16:46.997

Reputation: 79

2I want to ignore multiple without ignoring globally. This is specifically mentioned in the question. – Iszi – 2015-10-21T16:45:08.883

Select the entire column or contiguous columns; go back to top of columns while keeping selection and select "igore error" from displayed error box. – dbsdba – 2015-10-26T10:59:35.950

Nevertheless, I found this answer useful because none of the other solutions work if I want to ignore errors in entire columns when the errors don't apply to the top row (there's no warning icon/dropdown list if you select a column and the top cell doesn't have the error that applies to the rest of the column). So this answer should remain, since it does pertain to the title, which is most significant in determining which searches will lead to this topic. – Adi Inbar – 2016-10-05T20:24:30.043

About once a year I find myself reading this answer again. I wish I could upvote it every time. Thanks again! – Devil's Advocate – 2019-02-13T15:00:25.353

Here's a more precise version of the answer by @JosephSerido:

Select the range of cells where you want to ignore the error.
Use Tab, Shift+Tab, Enter, Shift+Enter to navigate within the selected range to a cell that has this error. Screenshot below where the first cell doesn't contain this error, so step 2 is required.
The clickable icon to ignore the error appears on the top left or top right of the selected range, depending on the scrollbar position. Don't confuse it with another icon on the bottom right.

Alternative method:

Select one cell with the error.
Press Ctrl+A once or twice, until the range you want is selected.
Click the icon, like in step 3 in the method above.

root

Posted 2012-12-31T02:16:46.997

Reputation: 272

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

– Dave M – 2018-03-22T12:14:48.880

We always appreciate the contributions from our community members, but please do not post comments as an answer. Please see the following Stack Exchange guidance regarding this scenario: Why do I need 50 reputation to comment? What can I do instead? Thanks for your help!

– Run5k – 2018-03-22T12:19:35.630

@DaveM This does provide an answer to the question. Moreover, this is the only fully working solution. What is lacking in your opinion? – root – 2018-03-22T15:16:21.520

1This is more a comment on the answer by @JosephSerido rather than a completely new answer – Dave M – 2018-03-22T23:19:47.727

@root This adds to an existing answer that has 41 up votes (at this time) It would seem to be better as a comment on that answer – Dave M – 2018-04-10T14:56:21.823

Excel for Mac:

Excel Menu > Preferences > Formulas and Lists > Error Checking >

Clear the 'Turn on background error checking' check box OR just 'Numbers Formatted as text' OR whatever may be the hindrance.

Mitch

Posted 2012-12-31T02:16:46.997

Reputation: 21

1I don't want to disable the error checking. I just want a way to tell Excel "this bunch is okay, quit bugging me about them". This was stated in the original Question post. – Iszi – 2017-11-30T15:14:35.303

In Excel, I believe you can fix the problem by selecting the entire column and selecting a format change.

mdpc

Posted 2012-12-31T02:16:46.997

Reputation: 4 176

2Changing format only does nothing at first glance. You also have to re-enter all values or use the text in columns option. But in this moment all binary values will lose their leading zeros. And thats what the OP wants to avoid. Unfortunately thats not a solution. – nixda – 2012-12-31T06:16:37.863

If you want it to apply to the whole work book (that is what I need for special work books) I use File>Options>Error Checking> uncheck the "Enable Background Error Checking" box. Save.

Excel 2010

Dianne

Posted 2012-12-31T02:16:46.997

Reputation: 9

1I've stated in the question that I don't want to globally disable checking - I just want to dismiss the errors found within a certain range of cells. – Iszi – 2017-11-30T15:16:48.170