This Excel tutorial explains how to verify Text format in a column. If it is a Number then convert Number to Text.
You may also want to read:
Excel verify Number format and convert Text to Number
Excel verify Text format
Three kinds of Cell contents are considered as Text
1) Non-number – such as alphabet, symbol
2) A mix of number and non-number
3) Number with small triangle on the top left of the Cell
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 Text, we have to make sure it is not Date and Number.
To check if it is a Number, Use IsNumer Function. IsNumber returns TRUE for Date and Number.
To check if it is a 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 Number to Text
1) Add ‘ in front of prefix
2) Add an assist column and type a formula as below
=A1&”” (assume A1 i s to convert to text)
Verify Text 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 “Text”, 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 text”.
Public Sub isNotText()
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 text") Then
Application.DisplayAlerts = False
Sheets("is not text").Delete
Application.DisplayAlerts = True
End If
Set newws = ActiveWorkbook.Worksheets.Add(before:=Worksheets(1))
newws.Name = "is not text"
newws.Range("A1").value = "Worksheet"
newws.Range("B1").value = "Range"
newws.Range("C1").value = "Format Type"
For Each Rng In dataTypeRng
If Rng.value = "Text" 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 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 = "Number"
counter = counter + 1
End If
Next
End If
Next Rng
If counter = 0 Then
dummy = MsgBox("All Text are in correct format", vbInformation)
Application.DisplayAlerts = False
Sheets("is not text").Delete
Application.DisplayAlerts = True
Else
newws.Columns("A:C").EntireColumn.AutoFit
sinput = MsgBox(counter & " Cell(s) contain non-text format" & Chr(10) & Chr(10) & "Do you want to automatically correct them now?" & Chr(10) & "(1) All Apostrophes in front of date will be removed and convert to number with Apostrophe" & Chr(10) & "(2) All Cell Format will be changed to General" & Chr(10) & "(3) All number will add an Apostrophe in the prefix", vbOKCancel + vbExclamation)
If sinput = 1 Then
For r = 2 To newws.Range("A" & Rows.Count).End(xlUp).Row
If newws.Range("C" & r).value = "Date" Then
sht.Range(newws.Range("B" & r).value).value = Application.WorksheetFunction.Clean(sht.Range(newws.Range("B" & r).value).value)
sht.Range(newws.Range("B" & r).value).NumberFormatLocal = "General"
sht.Range(newws.Range("B" & r).value).value = "'" & sht.Range(newws.Range("B" & r).value).Text
correctionCount = correctionCount + 1
ElseIf newws.Range("C" & r).value = "Number" Then
sht.Range(newws.Range("B" & r).value).NumberFormatLocal = "General"
sht.Range(newws.Range("B" & r).value).value = "'" & sht.Range(newws.Range("B" & r).value).Text
correctionCount = correctionCount + 1
End If
Next
dummy = MsgBox(correctionCount & " out of " & counter & " errors corrected", vbInformation)
End If
End If
errHandler: Exit Sub
End Sub
Demonstration- Identify and correct non-Text Cell
In the below example,
– A4, C2, C3 are Number
– A6 is a Date
– A7 is a Date with Apostrophe in prefix
– C5 is Time
The purpose of this Macro is to identify all Cells that are not number.
Run Macro isNotText()
Type in the data Type Range
Type in the data Range (in case you have header row)
A worksheet called “is not text” is created with a list of incorrect Cell address. Click Yes to correct format.
Corrected data
