Excel Convert Text to Numbers - Four easy methods

Posted on:  08/24/2021
Excel Convert Text to Numbers - Four easy methods

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

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.

Green triangle in Excel - convert text to number

Green triangle in Excel - convert text to number

Excel - Identify numbers stored as text

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

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 in Excel

Text to Columns dialog box

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:

  1. Type the number 1 in a blank cell and copy it.
  2. Select the text that needs to be converted.
  3. Right-click and do a Paste Special. Select Multiply and click OK.
Excel - multiply by 1 to change text to numbers

Multiply by 1 to change text to number

Other Excel articles

Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories