Excel verify Text format and convert number to text

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.

convert_text_to_number

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_text_to_number_04

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.

number_to_text_01

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.

number_to_text_01

Run Macro isNotText()

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 text” is created with a list of incorrect Cell address. Click Yes to correct format.

number_to_text_02

Corrected data

number_to_text_03

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in 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
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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