Problem: You have a field that may contain numbers or text. You need the numeric entries to sort with the text entries. Instead, Excel always sorts the numeric entries to the top of the list, followed by the text entries, as shown in Fig. 319.
Strategy: This is a rare case where you need to convert numeric entries to text entries.
If you were building this spreadsheet from scratch, you could have selected column A, and from the Format – Cells dialog, you could have formatted the column as Text, as shown in Fig. 320. This would allow all future entries to automatically be converted to text. However, converting cells to have a text format does NOT retroactively convert numbers to text.
Another option would be to edit each cell that contains a number. Select the cell. Hit F2 to edit the cell. Hit Home to move to the beginning of the cell. Type an apostrophe. Hit Enter (or the Down Arrow) to move to the next cell. This could get very tedious if you have more than a few cells to change.
The good news is, there are two easy methods for converting all of the entries in a column to text.
Method 1: Select all of the data in a column. From the menu, select Data – Text to Columns. In Step 1 of the Wizard, indicate that your data is fixed width, as shown in Fig. 321.
In Step 2 of the Wizard, you may or may not have any vertical lines drawn in the Data Preview section, as shown in Fig. 322. If you do, double-click to remove them.
In Step 3 of the Wizard, choose Text as the Column data format, as shown in Fig. 323.
After you choose Finish, the column will be converted to text.
Gotcha: You cannot sort using the AZ button in this case. You must select Sort from the Data menu. After you choose OK to sort, you will get the Sort Warning dialog shown in Fig. 324. Choose to Sort Numbers and Numbers Stored as Text Separately.
Alternate Strategy: You could also insert a temporary column with the following formula: =TEXT(A2,“@”).
Summary: Use Data – Text to Columns to convert a column to text.
Commands Discussed: Data – Text to Columns; Data – Sort
Images
