Problem: I have a field that can contain numbers and text. I need the numeric entries to sort with the text entries. However, Excel always sorts the numeric entries to the top of the list, followed by the text entries.
- Numbers sort before text that looks like numbers.
Strategy: This is a rare case in which you need to convert numeric entries to text entries.
If you were building this spreadsheet from scratch, you could select column A, select Home, Format, Format Cells, and then format the column as text. 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. To do this, you select the cell, press F2 to edit the cell, press Home to move to the beginning of the cell, and type an apostrophe. Then you press Enter to move to the next cell. This could get very tedious with more than a few cells to change.
The good news is that there is an easier method for converting all the entries in a column to text:
- Select all the data in a column. Select Data, Text to Columns. In step 1 of the Convert Text to Columns Wizard, indicate that your data is fixed width.
- In step 2 of the wizard, if you have any vertical lines drawn in the Data Preview section, double-click to remove them.
- In step 3 of the wizard, choose Text as the column data format.
- Click Finish. The column will be converted to text.
Alternate Strategy: You could also insert a temporary column with the formula =TEXT(A2,"@").