Need to convert text to numbers in Excel? I'll demonstrate four methods in the easy-to-follow article and video. If you need to analyze data in Excel, numbers frequently come in as text and need to be converted.

Excel convert text to numbers

The four methods to convert text to values

Method 1 - Green triangle

Method 2 - Value function

Method 3 - Text to columns

Method 4 - multiple by 1

YouTube video

The Green Triangle

A green triangle in the upper-left corner of a cell indicates an error in the formula in the cell. In our example below, columns C & D contain text that should be salaries or numbers.

Select the range and use the arrow next to the green triangle to convert to number.

Important note: If you want to use text in a cell - zip codes, social security numbers, customer numbers, and employee IDs should be text, not numbers - ignore the green triangle.

In Excel, numbers that are stored as text can cause unexpected results. It is important before you analyze numbers to make sure there are not stored as text. In this 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.

Use the Value Function to convert text to numbers

The second method to covert text to numbers is to use the VALUE function in Excel. The VALUE function converts a text string that represents a number to a number. For example, =VALUE(A2)

In our example below, I used the VALUE function in cell F2 and Autofilled down. You need to use Copy and Paste Values. This is shown at 1:34 in the video above.

VALUE Function in Excel

Text to Columns Feature

The third method to convert text to number is to use the Text to Columns feature located on the Data Tab in the Data Tools group. This method is shown at 2:00 in the video.

Text to Columns in Excel

Text to Columns dialog box

Multiply by 1 to convert text to numbers

Method 4 is to multiple by 1 to convert text to numbers. This is my favorite method since I can select multiply items at one time and I do not have to use copy and paste values. This method is shown at 2:36 in the video.

Steps:

Type the number 1 in a blank cell and copy it.

Select the text that needs to be converted.

Right-click and do a Paste Special. Select Multiply and click OK.

The UNIQUE function in Excel is one of my favorite functions. Unique returns a list of unique values in a range. The SORT function in Excel sorts the contents of a range or array.

Charts in Microsoft Excel are easy. This short video shows three tips for creating charts. I'll cover two keyboard shortcuts for making charts, using Recommend Charts, and creating charts using Quick Analysis.

Share this:

Chris Menard

Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with over 800 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 12 million viewers have appreciated. Menard also does public speaking at conferences for CPAs and Administrative Professionals. Connect with Chris on LinkedIn at chrismenardtraining.com/linked or on YouTube at chrismenardtraining.com/youtube