Categories
Excel

Convert Text to Numbers

The contents of a cell might look like numbers, especially in imported data, but it still might be impossible to use these numbers in calculations. Here are a few ways in which you easily can convert these “text” numbers to true numbers.

  1. Convert Text to Numbers with VALUE function
  2. Convert Text to Numbers with Paste Special
  3. Format Cells as Numbers
  4. Convert Text to Numbers with VBA/Macro

Use VALUE Function to Convert Text to Numbers

The VALUE function is used to convert a text string that represents a number into a numeric value. This can be useful when you have imported data from another source that contains numbers formatted as text, or when you want to perform calculations with numbers entered as text.

To use the VALUE function, you need to enter the following formula in a cell: =VALUE(text), where text is the text string that you want to convert or a cell reference. For example, =VALUE("99.99") will return the numeric value of the string. You can also use the cell reference, for example, for the text "99.99" in cell A1 you can enter =VALUE(A1) in another cell to get the numeric value 99.99.

Convert Text to Numbers with Paste Special

Use the Paste Special command to convert text values that look like numbers into numbers. Follow these steps:

  1. Copy a blank cell.
  2. Select the range of cells that contain the text values you want to convert.
  3. Right-click on the selection and choose Paste Special.
  4. In the Paste Special dialog box:
    • Select Values under Paste and
    • Select Add under Operation.
  5. Click OK.

This will change to true numbers any numbers that are being seen as text. This happens because a blank cell has a value of 0, and when you add any number to a number that Excel is treating as text, you will force the text number to become a true number.

Format Cells as Number

To convert the text to numbers by formatting cells, follow these steps:

  1. Select the cells that contain the text values that you want to convert.
  2. Right-click on the selected cells and choose Format Cells from the context menu.
  3. In the Format Cells dialog box, click on the Number tab and select Number from the Category list.
  4. Click OK to apply the format change and close the dialog box.

The text values should now be converted to numbers and you can perform calculations on them as usual.

Use Macro/VBA to Convert Text to Numbers

Another way to convert text to numbers is to use VBA, here are the steps to convert text to numbers using VBA:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the Project Explorer window, right-click on any sheet name and select Insert > Module.
  3. In the code window, write the following code:
Sub ConvertTextToNumbers()
 Dim rng As Range
 Dim cell As Range
 Set rng = Selection
 For Each cell In rng
  If IsNumeric(cell.Value) Then
   cell.Value = Val(cell.Value)
  End If
 Next cell
End Sub
  1. Close the Visual Basic Editor and return to Excel.
  2. Select the range of cells that contain the text values you want to convert.
  3. Press Alt+F8 (or click View > Macros) to open the Macro dialog box
  4. Select ConvertTextToNumbers macro and click Run button to execute it.

Formula and Functions

  1. Extract Number From Cells That Mix of Text and Numbers
  2. Convert Text to Numbers
  3. Using Code and Char Function
  4. Creating Dependent Combo Box Using Option Buttons
  5. Convert Formulas to Values