top of page

Finding Numbers Stored as Text

A common problem in Excel is the need to convert numbers stored as text in a column to the number format. Numbers stored as text may not give correct results when they are referenced in formulas, and they will not necessarily sort in order with other numbers in the same column that are correctly formatted [although Excel should prompt you to sort them as numbers].

If you have a very long list of numbers, it may take a lot of scrolling back and forth to find where numbers stored as text begin in a column. If you find the first instance of numbers stored as text in a column, you can select from that cell to the end of the column, and click, 'Convert to Number'in the drop down menu on the right. All of the numbers in the column will be correctly formatted.

However in a column with thousands of numbers it may be hard to find the first instance of a cell with numbers stored as text. Use the ISTEXT formula in an adjacent column to identify which numbers are being read by Excel as text.

The formula will give the result "TRUE" for cells that are in the text format .

bottom of page