| 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.
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]. |
||
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 | © MeadInKent.co.uk 2016 | CMIDX S3 P10 Y |