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.
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
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.
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.
Run Macro isNotNumber()
Type in the data Type Range
Type in the data Range (in case you have header row)
A worksheet called “is not number” is created with a list of incorrect Cell address. Click Yes to correct format.
Now you can see non-number Cell are corrected as number.
