Transformation of the text to a number in the Excel cell

When importing files or copying of the data with numeric values, you can often meet the problem: a number is converted to a text. As a result, formulas do not work and calculations become impossible. How you can fix this problem quickly? At the beginning we will see how to fix the error without macros.



How to convert the text in the number in Excel

Excel helps to an user to determine immediately, whether the values in the cells are formatted as the numbers or as the text. Numeric formats are aligned on the right, text - to the left.

When importing files or crashing in Excel to the numeric format becomes text, the green triangle appears in the upper left corner of the cells. This is the sign of error. The error occurs also if you put the apostrophe before the number.

before the number.

There are several methods of converting text to a number. So let`s consider the most simple and convenient methods.

  1. Use the button «Error» in the menu. When you highlight any cell with an error on the left, the corresponding icon appears. That`s what it is the button «Error». If you hover over it, the drop-down menu will appear (the black triangle). You need to select the column with numbers in text format. We open the menu of the button «Error» and click «Convert to Number».
    2. Convert to Number.
  2. You can apply any mathematical actions now. The simplest operations, that do not change the result (multiplication / division by one, adding / subtracting zero, raising to the first power, etc.) will do.
    3. simplest operations.
  3. You need to add to the special insert. You can also put into practice the simple arithmetic operation, but you do not need to create an auxiliary column here, but you do not need to create an auxiliary column. In the separate cell, to write the number 1 and to copy the cell in the clipboard (using the «Copy» button or the keyboard shortcut Ctrl + C). To highlight to the column with editable numbers. In the context menu of the «Paste» button, you need to click «Paste Special» and in the window that has opened to check the box next to «Multiply». After clicking OK, the text format is converted to a numeric format.
    4. Paste Special.
  4. Removing of non-printable symbols. Sometimes the numeric format is not recognized by the program because of invisible characters. We delete its using the formula that we enter into the auxiliary column. The CLEAN function removes to the unprintable characters, TRIM – are extra spaces, and the VALUE function converts a text format to a numeric one.
    5. CLEAN function.
  5. Using the «Text by Columns» tool, you need to select the column with the text arguments, that you want to convert in numbers. On the «DATA» tab we find the «Data Tools»-«Text by Columns» button, and after the « Convert Text to Columns Wizard» window opening, you need to click «Next». In the step-3, we pay attention to the column data format.
step-3.

The last method is suitable, if the values are in the same column.



The «Text-number» macro

You can convert to the numbers are saved as the text into the numbers with helping the macro.

There is the set of values stored in text format:

values stored in text format.

For inserting the macro, on the «DEVELOPER» tab we find the Visual Basic editor (ALT+F11). The editor window opens. For adding of the code, you need to press F7 and to paste the following code:

DEVELOPER.

To make it work, you need to save it, but the Excel workbook must be saved in the format that supports macros.

Now we come back to the page with the numbers: to select the column with the data and press the button «Macros». In the opened window – there is the list of available macros for this book, chose: «DEVELOPER»-«CODE»-«MACROS» the right one and click «Run».

Sub Conv()
    With ActiveSheet.UsedRange
        .Replace ",","."        
        arr = .Value
        .NumberFormat = "General"
        .Value = arr
    End With
End Sub
Macros.

The symbols moved to the right.

symbols to right.

Consequently, the values in the cells are «became» by the numbers.

Download example transformation text to number in Excel.

If in the column we can see the arguments with the certain number of decimal places (for example, 3.45), you may use to another macro.

Read also: How to translate the number and amount in words in Excel.

In this way, when you import or copy to the numeric data error you can easy to eliminate. To convert the text in a number you may in many ways (with and without macros) and to make it simple and fast. And subsequently with numerical arguments all necessary operations are performed.


en ru