Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Convert Text to Number

    How to Convert Text to Number

    AsmiBy AsmiFebruary 7, 2023 Excel 5 Mins Read

    When a number is formatted as text, the value looks numeric but acts as text. This means you cannot conduct any mathematical operation or use the data in a numeric formula.

    Fortunately, Excel has made it pretty convenient for users to transform data types while entering or manipulating data.

    Excel will display a warning icon when you select a cell where a number has been formatted as text. Even when you don’t select the cell, you will see a green indent on the top-left corner of the cell. This is to help users identify that their numeric value is formatted as text.

    After you’ve identified the cells, you can begin converting the texts to number.

    Error Wizard

    Convert to Number Error Wizard Excel

    The quickest way to convert a number to text in Excel is by using the error wizard.

    Select the cell with your numeric value and click on the warning icon. From the fly-out menu, choose Convert to Number.

    Although this method faster, it will not work the best for you if you want to convert a bigger data set. Head to the other methods we’ve discussed below to convert larger values in Excel.

    Via Text to Column

    You can use Text to Column to convert multiple cells from text to number format in Excel at the same time.

    If you’re dealing with a larger data set, you can use Text to Column to get the job done quicker.

    Although there isn’t a specific Number format you can convert your text to, you can format your value under General. General Excel is the default cell type. Your value will still be manipulated as a number if the format is under General.

    1. Select your data in the grid.
    2. Head to Data > Text to Column in the menubar.
      Text to Columns Excel
    3. Click Next twice in the Text to Column wizard.
    4. In the third window, make sure the selected format is General. Similarly, change the Destination cell to a different location.
      Convert Column Format Text to Column Excel
    5. Click OK.

    From what we’ve gathered, the Text to Column wizard will not convert your text value to a number if you paste it into its existing location.

    To test this, select your cell and hit F2 to edit your cell. When you click elsewhere, you will notice that the Error wizard appears again, alerting you that the cell is still formatted as text.

    Format Cells as Number

    You can use the Format Cells window to convert your text to a number. You can use this method to convert one or multiple values at the same time.

    Excel may, however, still display the error wizard even after you format your cell as a number.

    You can select the cell and hit enter to stop Excel from flashing the warning icon. Even if you don’t, don’t worry, Excel will still read your data as numbers.

    1. Select your value from the worksheet.
    2. Use Ctrl + 1 to open the Format cell window.
    3. From the sidebar, head to Number.
      Number Category Format Cells Excel
    4. Select OK.
      Format Cell as Number Excel

    Use Functions

    You can use two functions, VALUE and NUMBERVALUE to convert a text to a number in Excel.

    These functions work similarly, except NUMBERVALUE is locale independent.

    Similarly, the NUMBERVALUE function will remove all spaces between each character. Depending on your needs, you can use either the VALUE or NUMBERVALUE function.

    VALUE Function

    On an empty cell, enter the VALUE function in the following format:

    =VALUE(cell)

    Value function Excel

    In this example, we’ve converted 5 numbers in the text format using the VALUE function. Firstly, we passed cell A2 as an argument and then used Flash Fill to fill in the remaining values.

    NUMBERVALUE

    Enter the NUMBERVALUE function in the following format:

    =NUMBERVALUE(cell)

    Numbervalue function Excel

    Here, we’ve entered the formula in cell F2. We passed B2 as an argument and used Flash Fill to convert the remaining values.

    Use Paste Special to Paste Data as a Number

    If you’re copy-pasting the values from a different spreadsheet, you can use Paste Special to convert your text to numbers.

    Paste Special is a great tool to modify data even before pasting them to your grid. You can use this configuration to transform your value.

    1. Open your workbook.
    2. On the cell you wish to paste your value, enter 1. You must enter 1 for each value while converting multiple data.
    3. Copy your value, and right-click on the first cell with 1.
      Copy Data Excel
    4. Choose Paste Special.
      paste special Excel
    5. In the Paste Special window, select the options for All under Paste, and Multiply under Operations.
      Paste special window excel
    6. Click OK.

    Quick tip: Place your cursor on the bottom-right corner of the cell with 1. When the cursor shifts to plus, drag it horizontally or vertically for Excel to automatically enter 1 in the selected columns.

    Through Power Query to Transform Value

    Although using the Power Query may seem intimidating, it’s personally our favorite method to convert large text datasets to numbers in Excel.

    If all numeric data in your spreadsheet was transformed into text, your best bet is to load your data to the Power Query.

    Power Query will automatically convert your data to General when you load it to your sheet.

    1. Select your data from the worksheet.
    2. Head to the Data tab and select From Table in the Get & Transform section.
      New Query from Table Excel
    3. Click OK in the Create Table prompt.
      Create Table Excel
    4. Select Close and Load from the home tab.
      Close and Load Power Query Excel
    Cell Formats Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Error Wizard
      • Via Text to Column
      • Format Cells as Number
      • Use Functions
        • VALUE Function
        • NUMBERVALUE
      • Use Paste Special to Paste Data as a Number
      • Through Power Query to Transform Value
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.