Excel verify Number data type and convert Text to Number

This Excel tutorial explains how to verify if a column contains only Number data type. If it is a Text then convert Text to Number.

Excel verify Number data type

Number can be in two forms in Excel – Text and Number. If a Number is a Text, there is a small triangle on the top left of the Cell.

By default, number in Text format aligns to the left while Number aligns to the right, but you can change the alignment.

There are three kinds of data in Excel – Date, Number and Text

To check whether the data is number, we have to make sure it is not Text and Date.

To make sure it is not a Text, Use IsNumer Function. IsNumber returns TRUE for Date and Number.

To make sure it is not  Date, use IsDate Function, Number is not considered as Date. Unfortunately, IsDate is for VBA only, we need to workaround with Cell Function for worksheet.

If the format is Date, Cell Function returns a value from D1 to D9, depending on the date format.

Now to combine IsNumber and Cell Function,

=IF(ISNUMBER(A2),IF(LEFT(CELL("Format",A2),1)="D",TRUE,FALSE))

Convert Text to Number

To convert Text to Number,select B2:B4, and then find the exclamation mark in B2, select “Convert to Number”.

Mass verification of Number data type (VBA)

Assume that you have hundreds of rows and columns with different required format, how should you check the data type?

First of all, create a row (row 1 in the example) that specifies the required data type for each column.

Now we can create a Macro that loops through row 1. If the required format is “Number”, then check  the data of the whole column whether it is a Text. The yellow Cells are in incorrect format that we want to identify in a new worksheet called “is not number”.

Public Sub isNotNumber()
On Error GoTo errHandler
    Set dataTypeRng = Application.InputBox("Input data Type Range", Type:=8)
    dataBeginRow = Application.InputBox("Input data begin row")
    dataEndRow = Application.InputBox("Input data end row")
    
    For Each Rng In dataTypeRng
        If Rng.Value = "Number" Then
            col_letter = Split(Rng.Address, "$")(1)
            For Each rng2 In Range(col_letter & dataBeginRow & ":" & col_letter & dataEndRow)
                If Not IsEmpty(rng2) And IsDate(rng2) Then
                    txt = txt & Replace(rng2.Address, "$", "") & " (Date)" & Chr(10)
                ElseIf Not IsEmpty(rng2) And Not Application.WorksheetFunction.IsNumber(rng2) Then
                    txt = txt & Replace(rng2.Address, "$", "") & " (Text)" & Chr(10)
                End If
            Next rng2
        End If
    Next Rng

    If txt <> "" Then
        For Each ws In ActiveWorkbook.Worksheets
            If Not wsExists("is not number") Then
                Set newWS = ThisWorkbook.Worksheets.Add
                newWS.Name = "is not number"
            End If
        Next ws
        Sheets("is not number").Select
        Sheets("is not number").Range("A1").Value = txt
    Else: MsgBox ("All Number format are correct")
   End If
    
errHandler: Exit Sub
End Sub

Function wsExists(wksName As String) As Boolean
    On Error Resume Next
    wsExists = CBool(Len(Worksheets(wksName).Name) > 0)
    On Error GoTo 0
End Function

Demonstration- Identify non-Number Cell

Run Macro

Type in the data Type Range

Type in the data being row

Type in the data end row

A worksheet called “is not number” is created, with the incorrect type inside the brackets. If you want to display them in A1, A2, A3… just copy the values to Notepad and then copy and paste back into Excel. I don’t want to create a Macro to correct the format because I think it is necessary to read Cell by Cell to understand what is causing the mistakes.

 

 

Excel verify Number data type and convert Text to Number