Find Text Numbers

September 20, 2021 - by Bill Jelen

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: 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.

Here is a report. There are at least five cells that look like they contain numbers, but the green triangles in those cells are telling us something is wrong. — Figure 82. 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.

Open the Go To Special dialog. Choose Constants. Four checkboxes appear for Numbers, Text, Logicals, and Errors. Clear the checkboxes from three of them, leaving you only Text selected. Click OK. — Figure 83. 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.

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. Gothca: This method can be insanely slow if the range being converted is used in other formulas.

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

1. Copy any blank cell to plaze a zero on the clipboard.
2. Highlight the text cells.
3. 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.

Open the Exclamation point drop-down that appears to the left of the first text number. Choose Convert to Number — Figure 84. 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. It will convert all of the text numbers and then do a single recalculate. It is much faster than using the Convert to Number command.

This article is an excerpt from Power Excel With MrExcel

Title photo by Mick Haupt on Unsplash