Excel verify Number format and convert Text to Number

This Excel tutorial explains how to verify Number format in a column. If it is a Text then convert Text to Number.

You may also want to read:

Excel verify Text format and convert number to text

Excel Cell Function to check Cell format, address, contents

Excel verify Number format

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.

convert_text_to_number

There are basically 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.

The following formula returns TRUE if it is a Date

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

convert_text_to_number_04

Convert Text to Number

convert_text_to_number_02

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

Verify Number format (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.

text to number__11

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

Public Sub isNotNumber()

    Set sht = ActiveSheet
On Error GoTo errHandler
    Set dataTypeRng = Application.InputBox("Select data Type Range", Type:=8)
    Set dataRng = Application.InputBox("Select data range", Type:=8)
    
    dataStRow = Split(Split(dataRng.Address, ":")(0), "$")(2)
    dataEdRow = Split(Split(dataRng.Address, ":")(1), "$")(2)
    
    If wsExists("is not number") Then
        Application.DisplayAlerts = False
        Sheets("is not number").Delete
        Application.DisplayAlerts = True
    End If
        Set newws = ActiveWorkbook.Worksheets.Add(before:=Worksheets(1))
        newws.Name = "is not number"
        newws.Range("A1").value = "Worksheet"
        newws.Range("B1").value = "Range"
        newws.Range("C1").value = "Format Type"
    
    For Each Rng In dataTypeRng
        If Rng.value = "Number" Then
            col_letter = Split(Rng.Address, "$")(1)
            For r = dataStRow To dataEdRow
                If Not IsEmpty(sht.Range(col_letter & r)) And IsDate(sht.Range(col_letter & r)) Then
                    nextrow = newws.Range("A" & Rows.Count).End(xlUp).Row + 1
                    newws.Range("A" & nextrow).value = sht.Name
                    newws.Range("B" & nextrow).value = col_letter & r
                    newws.Range("C" & nextrow).value = "Date"
                    counter = counter + 1
                ElseIf Not IsEmpty(sht.Range(col_letter & r)) And Not Application.WorksheetFunction.IsNumber((sht.Range(col_letter & r))) Then
                    nextrow = newws.Range("A" & Rows.Count).End(xlUp).Row + 1
                    newws.Range("A" & nextrow).value = sht.Name
                    newws.Range("B" & nextrow).value = col_letter & r
                    newws.Range("C" & nextrow).value = "Text"
                    counter = counter + 1
                End If
            Next
        End If
    Next Rng

    If counter = 0 Then
        dummy = MsgBox("All numbers are in correct format", vbInformation)
        Application.DisplayAlerts = False
        Sheets("is not number").Delete
        Application.DisplayAlerts = True
    Else
        newws.Columns("A:C").EntireColumn.AutoFit
        sinput = MsgBox(counter & " Cell(s) contain non-number format" & Chr(10) & Chr(10) & "Do you want to automatically correct them now?" & Chr(10) & "(1) All Apostrophes in front of number will be removed" & Chr(10) & "(2)All Cell Format will be changed to General", vbOKCancel + vbExclamation)
        If sinput = 1 Then
            For r = 2 To newws.Range("A" & Rows.Count).End(xlUp).Row
                    sht.Range(newws.Range("B" & r).value).value = sht.Range(newws.Range("B" & r).value).Text
                    sht.Range(newws.Range("B" & r).value).NumberFormatLocal = "General"
            Next
            dummy = MsgBox(counter & " errors corrected", vbInformation)
        End If
    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 and correct non-Number Cell

In the below example, B3 is a date, B5 is a date with Apostrophe in prefix. D2 is a number with Apostrophe.

The purpose of this Macro is to identify all Cells that are not number.

text to number__11

Run Macro isNotNumber()

Type in the data Type Range

text to number__12

Type in the data Range (in case you have header row)

text to number__13

A worksheet called “is not number” is created with a list of incorrect Cell address. Click Yes to correct format.

text to number__14

Now you can see non-number Cell are corrected as number.

text to number__15

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *