Elite Membership

Convert Text To Numbers In Excel

Written by Jeevan A Y Jeevan A Y Freelance Writer Jeevan, a seasoned data expert with 7 years in MIS reporting, excels in Advanced Excel, VBA, Power BI, and SQL. Currently an Assistant Manager MIS, his insightful data storytelling drives swift decision-making. 7+ years of experience MBA (Finance & Marketing) MIS Reporting View Full Profile
Reviewed by Dheeraj Vaidya, CFA, FRM Dheeraj Vaidya, CFA, FRM Content Reviewer & Course Director A former J.P.Morgan and CLSA Equity Analyst, Dheeraj specializes in financial modeling, AI, forecasting, and valuations. In his career spanning almost two decades, he has trained and mentored more than 100,000 students and professionals on a range of topics. 20+ years of experience CFA, FRM, IIT Delhi, IIM Lucknow Financial Modeling View Full Profile
Updated Dec 19, 2024
Read Time 5 min

What Is Convert Text To Numbers In Excel?

Convert Text to Numbers in Excel is a feature where we convert a cell with text, or strings, to the pre-set Excel numeric values to perform arithmetic calculations.

Excel Text To Numbers Conversion is important because some calculations may not execute or return errors, since they may be a numerical string, i.e., numeric values formatted as text.

Download FREE Convert Text To Numbers In Excel Excel Template and Follow Along!
Download Excel Template

Key Takeaways

  • The Convert Text to Numbers in Excel helps users totransform an existing numeric string to a proper numeric value because we cannot use a numeric string that looks like a number but is formatted as text to perform mathematical calculations.
  • We can perform the conversions using the error handle box that appears on the incorrect output cell, and selecting the “Convert to Number” option.
  • Other ways to Convert Text to Numbers are using the Paste Special and Text to Column methods, and by using the VALUE function.

How To Convert Text To Numbers In Excel?

We can Convert Text to Numbers in Excel using the following ways, namely:

  1. Using quick convert text to numbers Excel option.
  2. Using Paste special cell formatting method.
  3. Using the Text to Column Method.
  4. Using the VALUE function.

Examples

We will consider some examples for Convert Text To Numbers In Excel using the above-mentioned methods.

Example #1 – Using Quick Convert Text to Numbers Excel Option

It is probably the simplest of ways in Excel. Many people use the apostrophe ( ‘ ) before entering the numbers in Excel.

The steps toConvert Text to Numbersare,

  1. We must first select the data.
  2. Then, click the error handle box, and select the “Convert to Number” option.
  3. That would instantly convert the text-formatted numbers to number format, and now the SUM function works well and shows the accurate result, as shown below.

Example #2 – Using Paste Special Cell Formatting Method

Another way to change the text to numbers is using the Paste Special method. Again, consider the same data we used in the previous example. The steps are,

  • Step 1: : First, we must type either 0 or 1 in any cell.
Using Cell Formatting Method step 1
  • Step 2: Now, copy that number. (We have entered the number 1 in cell C2).
Using Cell Formatting Method step 2
  • Step 3: Now, we must select the numbers list.
Using Cell Formatting Method step 3
  • Step 4: Now, we must press ALT + E + S (Excel shortcut key for the Paste Special method). That will open up the below dialog box. Select the multiply option. (We can try to divide also).
Using Cell Formatting Method step 4
  • Step 5: As a result, it would instantly convert the text to numbers, and the SUM formula is working well now, as shown below.
Using Cell Formatting Method step 5

Example #3 – Using the Text to Column Method

It is the third method of converting text to numbers. It is a bit lengthier process than the earlier two, but having as many alternatives as possible is always good. The steps are,

  • Step 1: We must first select the data.
Using Text to Column Step 1
  • Step 2: Then, click the “Data” tab and the “Text to Columns” option.
Using Text to Column Step 2
  • Step 3: As a result, it will open up the below dialog box and ensure “Delimited” is selected. Click on the “Next button.”
Using Text to Column Step 3
  • Step 4: We must ensure the “Tab” box is checked and click on the “Next” button.
Using Text to Column Step 4
  • Step 5: In the next window, we must select the “General” option, select the destination cell, and click the “Finish” button.
Using Text to Column Step 5
  • Step 6: Consequently, this would convert text to numbers, and SUM will work, as shown below.
Using Text to Column Step 6

Example #4 – Using the VALUE Function

In addition, a formula can Convert Text to Numbers in Excel. The VALUE function can perform the job for us. We must follow the below steps to learn how to do it.

  • Step 1: First, we must apply the VALUE formula in cell B1.
Using VALUE Function step 1
  • Step 2: We must drag and drop the formula into the remaining cells.
Using VALUE Function step 2
  • Step 3: Then, apply the SUM formula in cell B6 to check whether it has converted or not.
Using VALUE Function step 3

The output is shown above.

Important Things To Note

  • If we find the green triangle button in the cell, there must be something wrong with the data.
  • The “Text to Column” can also correct dates, numbers, and time formats.

Frequently Asked Questions (FAQs)

Where is the VALUE function in Excel found?

To insert the VALUE function, use the following path.

First, choose an empty cell → select the “Formulas” tab → go to the “Function Library” group → click the “Text” option drop-down → select the “Value” function, as shown below.

Convert Text To Numbers In Excel FAQ 1.png
 

Why does Convert Text to Numbers in Excel not working?

The Convert Text to Numbers in Excel may not work for the following reasons,
• The cell value is a numeric value formatted as text, or the output cell is not in the number format.
• In the VALUE function, we have given a cell range instead of the cell value or one single cell reference, because the function accepts only one argument at a time. Once we get the right output, we can then drag the formula for the rest of the cells to fix the errors.

What is the use of the VALUE function in Excel Text to Numbers Conversion?

• The VALUE function is used to convert the text string that represents a number to a number.
• If spacing problems exist, we nest the VALUE function with the TRIM function.
For example, =Trim(Value(A1))

Recommended Articles

This article is a guide to Convert Text to Numbers in Excel. Here we use Error handler, text to column, PASTE special, VALUE, examples & downloadable template. You may also look at these useful functions in Excel: –