Home	Excel	Access
\| Links \| Formatting numbers \| Alphabetical Index \|

An Excel macro to convert text values to numbers

Excel can be used to collect lists of data from other sources. Users may paste data they have copied from other reports and programs. This can sometimes result in problems with some values which are required to be numbers appearing in cells as text values. This can corrupt other calculations and processes.

Example

The values on rows 4 and 8 have been incorrectly input as text. This is not just a formatting issue. There is also an error in cell B10 where a spurious text character has been added.

A macro can be used to convert any appropriate text values into numbers. The macro program below can be entered in a visual basic module. A new macro called MyConvNum() will then be available. It will change any values within a currently selected range (i.e. highlighted cells) from text into numbers.

Sub MyConvNum()
' examines each cell in a selected range and converts
' any numbers formatted as text into numbers

Dim Bcell As Range, MyValErr As String, MyVal As Double
Dim ActSheet As Worksheet, SelRange As Range

On Error GoTo MyErrBit
Set ActSheet = ActiveSheet
Set SelRange = Selection

For Each Bcell In SelRange
If WorksheetFunction.IsNumber(Bcell.Value) Then
Bcell.Font.Bold = False ' optional
Else
MyValErr = "N"
MyVal = CDbl(Bcell.Value)
If MyValErr = "N" Then
    If Not IsEmpty(Bcell) Then
      Bcell.Value = MyVal
    End If
Else
    Bcell.Font.Bold = True ' optional
End If
End If
Next Bcell

MyErrBit:
MyValErr = "Y"
Resume Next
End Sub

1 Highlight a range of cells

2 Select option [Developer] Code | Macros | and then MyConvNum [Run]

3 If the Developer tab is not visible within Excel, [read this].

Click to download this useful PDF document

before >

after selecting cells B4:C10 and running the MyConvNum macro >

Note that the macro has highlighted the text value in cell B10 in Bold. This action may not be appropriate for your purposes and the two lines marked as 'optional' in the macro program may be deleted.

Get this information as a document
accompanied by Excel worksheets

Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: excel_text_numbers.htm

Page last updated: Feb 14

CMIDX S3 P10 Y