Excel Convert Text to Numbers - Four easy methods
Posted on: 08/24/2021
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.
Green triangle in Excel - convert text to number
Excel - Identify numbers stored as text
-
Excel - 5 easy ways to identify numbers stored as text
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.
Multiply by 1 to change text to number
Chris Menard
Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers.
Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff 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.
Categories