sc_excel_IdAsTextHeader_img01In this example we are going to convert Id numbers into Text format and we will look at how to removing any resulting warning messages.

Tools used for this exercise:

  • Using the “Number as text” format
  • Removing the warning message on the Options command on the File Tab.

Suppose we have the following list of ids and names:
sc_excel_IdAsText_img01
(The ids were generated randomly. The first names and last names were generated using the most popular names from various countries; first names and last names were then paired randomly.)

Task: Converting the Numbers in the Id Column to Text.

Notice that the default format for the column of numbers is to be aligned to the right of the cells. We are not going to be doing any calculations on these and they should be treated as text.

First select the cells:
sc_excel_IdAsText_img02
Then in the “Number” group of the Home Tab use the drop down arrow:
sc_excel_IdAsText_img03

And select “Text”:
sc_excel_IdAsText_img04
Now we can treat the values as text.

Notice that the contents have shifted and are now aligned to the left of the cells:
sc_excel_IdAsText_img05
(Also note that, when a cell is selected the value in the “Number” group is “Text”).

Task: Removing the Error Message.

If you edit any of the ids you will get a small triangle in the corner of the cell.

For example, suppose that we need to modify the first Id to from 416082 to 416000:
sc_excel_IdAsText_img06

If you click on the small triangle a yellow diamond will appear:
sc_excel_IdAsText_img07

When you click on the Yellow diamond the following will appear:
sc_excel_IdAsText_img08
In this case, it just an information message and a reminder that the values are stored as text. If you want to remove the message just select “Ignore Error”.

A problem arises when we make changes to the spread sheet such are sorting or filtering the data.

For example, suppose we now sort the data by last name (this was done by using the “Sort&Filter” command on the Home tab). In the results, the error triangle on the Id number that was changed, has returned:
sc_excel_IdAsText_img09

Removing the Error:

To remove this on a more permanent basis go to the options command on the File tab:
sc_excel_IdAsText_img10
In the side bar, click on the “Formulas” entry. Here is an overview of the window:
sc_excel_IdAsText_img11
Near the bottom of the screen, un-check the “Numbers formatted as text or preceded by an apostrophe” option:
sc_excel_IdAsText_img12
Since we know that the warning message is not need in this case, turning is off is not an issue. However, be aware that this has been turned off for all subsequent workbooks you open. If you need to check for numbers formatted as text in other workbooks, just turn the option back on.

The Results:

Here is the final result with the warning messages turned off and the “Last Names” sorted in alphabetical order.
sc_excel_IdAsText_img13