How to Use VBA and Formulas to Convert Text to Numbers in Excel

Sometimes, you may encounter numbers that are stored as text in Excel, which can cause problems when you try to perform calculations or apply formatting on them. For example, you may have imported some data from a text file or a web page that contains numbers with commas, spaces, or other characters that Excel does not recognize as numeric. In this article, we will show you how to convert numbers stored as text to regular numbers using VBA and Excel formulas.

Numbers stored as text are treated as text strings by Excel, which means they cannot be used in arithmetic operations, comparisons, or functions that expect numeric arguments. To convert them to regular numbers, you need to remove any non-numeric characters and change the data type of the cells from text to number. There are several ways to do this, such as using the Paste Special command, the Text to Columns wizard, or the VALUE function. However, these methods may not be efficient or convenient if you have a large range of data or if you want to automate the process. In that case, you can use VBA to create a macro that can convert numbers stored as text to regular numbers in one go.

Procedures

To convert numbers stored as text to regular numbers using VBA, you can follow these steps:

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 or clicking the Visual Basic button on the Developer tab.
2. Insert a new module by clicking Insert > Module or right-clicking on the Project Explorer and selecting Insert > Module.
3. In the module window, type or copy the following code:

Sub ConvertTextToNumber()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    'Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets
        'Set the range to the used cells in the worksheet
        Set rng = ws.UsedRange
        'Loop through each cell in the range
        For Each cell In rng
            'Check if the cell contains a number stored as text
            If IsNumeric(cell) And cell.NumberFormat = "@" Then
                'Convert the cell value to a number by multiplying it by 1
                cell.Value = cell.Value * 1
                'Change the cell number format to General
                cell.NumberFormat = "General"
            End If
        Next cell
    Next ws
End Sub

4. Save the macro by clicking File > Save or pressing Ctrl + S.
5. Run the macro by clicking Run > Run Sub/UserForm or pressing F5. Alternatively, you can assign the macro to a button or a keyboard shortcut for easy access.

Explanation

The code above works as follows:

• The first line declares a variable named ws as a Worksheet object, which will store each worksheet in the workbook.
• The second line declares a variable named rng as a Range object, which will store the range of used cells in each worksheet.
• The third line declares a variable named cell as a Range object, which will store each cell in the range.
• The fourth line starts a For Each loop that iterates through all the worksheets in the workbook, using the ws variable to refer to each worksheet.
• The fifth line sets the rng variable to the used range in the current worksheet, using the UsedRange property of the ws object.
• The sixth line starts another For Each loop that iterates through each cell in the range, using the cell variable to refer to each cell.
• The seventh line uses the IsNumeric function and the NumberFormat property to check if the cell contains a number stored as text. The IsNumeric function returns True if the cell value can be converted to a number, and the NumberFormat property returns “@” if the cell is formatted as text.
• The eighth line converts the cell value to a number by multiplying it by 1, using the Value property of the cell object. This removes any non-numeric characters and changes the data type of the cell from text to number.
• The ninth line changes the cell number format to General, using the NumberFormat property of the cell object. This applies the default number format to the cell, which can display integers, decimals, percentages, dates, or times depending on the value.
• The tenth line ends the inner For Each loop and moves to the next cell in the range.
• The eleventh line ends the outer For Each loop and moves to the next worksheet in the workbook.

Example

To illustrate how the macro works, let’s use the following example data:

Suppose this data is stored in Sheet1 of a workbook, and the Salary column contains numbers stored as text, as indicated by the green triangles in the upper-left corners of the cells. If we try to calculate the average salary using the formula =AVERAGE(C2:C6), we will get an error value of #DIV/0!, because Excel cannot perform calculations on text values.

To fix this, we can run the macro that we created earlier, which will convert the numbers stored as text to regular numbers and change the number format to General. After running the macro, the data will look like this:

Now, if we use the same formula =AVERAGE(C2:C6), we will get the correct result of 70000, which is the average salary of the five employees.

Other Approaches

Besides using VBA, there are other ways to convert numbers stored as text to regular numbers in Excel, such as:

• Using the Paste Special command: You can copy any blank cell, select the range of numbers stored as text, right-click and choose Paste Special, and select Add in the Operation section. This will add zero to each cell, which will convert the text values to numbers and remove the non-numeric characters.
• Using the Text to Columns wizard: You can select the range of numbers stored as text, go to the Data tab and click Text to Columns, choose Delimited in the first step, click Next twice, and select General in the third step. This will split the text values into columns based on the delimiter characters, such as commas or spaces, and convert them to numbers with the General format.
• Using the VALUE function: You can enter the formula =VALUE(text) in a blank cell, where text is the cell reference or the text value that you want to convert to a number. This will return the numeric value of the text, which you can then copy and paste as values over the original data.