Excel: Find Text Numbers

Problem: I suspect that there are cells in my data that contain text numbers instead of numbers. I know that numbers entered as text cause a variety of problems. For example, although a formula such as =E3+E4 will include the text number in E3, most functions, such as SUM or AVERAGE, will ignore the text cells. Text versions of a number will sort to a different place than numeric versions. If I use a MATCH or VLOOKUP function, a text version of 3446 will not match a numeric version of 3446. How can I find text entries that need to be converted to numbers?

Strategy: In versions before Excel 2002, there was no easy way to visually locate these cells. In versions of Excel from Excel 2002 through Excel 2010, these text cells, as well as a variety of other potential errors, are noted by a dark green triangle in the upper-left corner of the cell. As shown below, cells C6, E2, E3, E6, and E7 have triangles in their upper-left corners because they are text entries that look like numbers.

1. Some cells contain text that looks like numbers.

   Instead of looking for those little triangles, here's an easier way to locate all the text entries so you can convert them to numbers:

   1. Select the entire range of data by selecting one cell and then pressing Ctrl+*.
   2. Select Home, Find & Select, Go To Special. Excel will display the Go To Special dialog.
   3. Select Constants. Deselect the options Numbers, Logicals, and Errors, leaving only Text selected.

   

2. Choose Constants. Deselect Numbers.

   Results: All the text entries will be highlighted.

   Additional Details: There are a number of ways to convert these cells from text to numbers. The easiest way is to get all the text cells in one contiguous range. If you can sort the data by column E descending, all the text entries will sort to the top of the list.

   In Excel 2002 and newer versions, you can convert a contiguous range of text numbers. To do so, you use the Error (exclamation point) dropdown and select Convert to Number. This method works only if the top-left cell in your selection contains a number stored as text.

   For earlier versions of Excel, you can use the following trick:

   1. Enter a zero in a blank cell.
   2. Copy the cell with the zero by using Ctrl+C.
   3. Highlight the text cells.
   4. Choose Edit, Paste Special. In the Paste Special dialog that appears, select Values and Add and then click OK.

   Adding a zero to the text cells will cause them to be converted to real numbers.

   

3. Open the error dropdown to convert text to numbers.

   Alternate Strategy: The fastest way to convert a column of numbers to text is to select the column and type Alt+DEF (that is, Alt+D followed by E then F). This little command uses the default Text to Columns settings which will convert your text to numbers.