Excel - 5 easy ways to identify numbers stored as text

Posted on: 08/18/2021

In Excel, numbers that are stored as text can cause unexpected results. It is important before you analyze numbers to make sure there are no numbers or values stored as text. This detailed article with a video shows 5 methods for identifying numbers stored as text.

Excel identify numbers stored as text - conditional formatting method — **Identify numbers stored as text**

In this article and YouTube video, I'll demonstrate five methods to quickly find numbers stored as text. Numbers as text usually happens when you pull data from another data source into Excel.

YouTube video

Download the Excel file used in the video.

Method 1 - the Green triangle

A green triangle in the upper-left corner of a cell indicates an error in the formula in the cell. If you select the cell, the Trace Error Button image button appears. Click the arrow next to the button for a list of options.

When you click the drop-down arrow, the first option is letting you now Excel identified a Number Stored as Text. You do not always get the green triangle when you have numbers stored as text. To convert, click the option, Covert to Number.

Method 2 - Use the Status Bar

The Status Bar shows at the bottom of Excel. It reads Ready in the left corner and has your Zoom, for example, 120%, in the right corner. Several features turned on by default in the Status Bar - Sum, Average, and Count.

If you select two numbers, you can right-click the word Sum (or any of the words) and turn on other features. I recommend you turn on Numerical Count, Maximum, and Minimum. They will show up for this worksheet, the workbook you are working in, and all future workbooks.

Numerical count is the COUNT function - the total number of numbers you have (it is not counting text). Count is working as the COUNTA function. It is counting text and numbers. With both turned on, you know if you have text mixed with your numbers or values. See the screenshot below.

**Status Bar in Excel - Count and Numerical Count**

Method 3 - ISNUMBER Function

Use the ISNUMBER function to check if a value is a number. It returns TRUE if it is a number and FALSE if it is not a number.

Zoom Articles

ISNUMBER function - true or false — **ISNUMBER function returns True or False**

Method 4 - ISTEXT Function

Use the ISTEXT function to check for a text value. ISTEXT returns TRUE if text, and FALSE if not text.

ISTEXT function in Excel — **ISTEXT Function in Excel**

Method 5 - Conditional Formatting

Conditional Formatting is the best method actually to see the text stored as numbers.

Steps to use conditional formatting to identify text:

Select the range of cells. Example: i2:i36
On the Home tab, select Conditional Formatting.
Click New Rule.
Select Use a formula to determine which cells to format.
Type the formula =istext($i2)
Click Format
Click the Fill tab
Select a color and click OK
Click OK again.

**Conditional formatting - ISTEXT function**

Conditional Formatting — **Cell showing conditional formatting**

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.