|| Links | Formatting numbers | Alphabetical Index ||
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.
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.
' 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
MyValErr = "N"
MyVal = CDbl(Bcell.Value)
If MyValErr = "N" Then
If Not IsEmpty(Bcell) Then
Bcell.Value = MyVal
Bcell.Font.Bold = True ' optional
MyValErr = "Y"
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].
|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
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|