MeadInKent
| 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.

  numbers as text

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].

save this free reference page with examples of 8 Excel functions

Click to download this useful PDF document

 

before > numbers and text after selecting cells B4:C10 and running the MyConvNum macro > convert text to numbers

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.


excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format 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 MeadInKent.co.uk 2016 CMIDX S3 P10 Y