How to Use Excel VBA to Convert a Number to Text (4 Examples)

The dataset below represents the published article numbers of some writers on a content publishing site named ExcelDemy. The numbers are in number format. We’ll convert them to text format using VBA.

Method 1 – Use VBA Cstr Function 

Steps:

• Right-click on the sheet title.
• Select View Code from the Context menu.

A VBA window will open up.

• Enter the following code:

Sub ConvertTo_Text()
Debug.Print "ExcelDemy"
Debug.Print 2
Debug.Print CStr(2)
End Sub

• Press Ctrl+G to open the Immediate Window.
• Click the Run icon to run the codes.

• Select the macro name as specified in the codes.
• Press the Run tab.

The Print function changed the format of text ‘ExcelDemy’ to text format and ‘2’ to Number format but the Cstr function converted the format of 2 to text format. The text format remains aligned to the left, and the number format remains aligned to the right in Excel.

Method 2 – Apply VBA Cstr Function with Selected Range

Steps:

• Select View Code from the Context menu after right-clicking on the sheet title.

• The VBA window will open.
• Enter the following code:

    Sub Convert_to_Text(ByRef xRange As String, Optional ByVal W_Sheet As Worksheet)
        Dim TP As Double
        Dim V_Range As Range
        Dim xCell As Object
        If W_Sheet Is Nothing Then Set W_Sheet = ActiveSheet
            Set V_Range = W_Sheet.Range(xRange).SpecialCells(xlCellTypeVisible)
            For Each xCell In V_Range
                If Not IsEmpty(xCell.Value) And IsNumeric(xCell.Value) Then
                    TP = xCell.Value
                    xCell.ClearContents
                    xCell.NumberFormat = "@"
                    xCell.Value = CStr(TP)
                End If
            Next xCell
    End Sub
    Sub xMacro()
        Call Convert_to_Text("C5:C9", ActiveSheet)
    End Sub

• Click the Run icon to run the codes, and a Macro dialog box will open.

• Click the macro name and press Run.

The numbers are converted and stored as text. If the numbers are stored as text, a triangle-shaped green icon appears in the upper-left corner of the cell.

Method 3 – Use VBA Cstr Function with Selection

Steps:

• Select the data range C5:C9.
• Right-click on the sheet title.
• Select View Code from the Context menu.

You will get the VBA window.

• Enter the following codes and click the Run icon.

Sub Numer_To_Text()
For Each cell In Selection
If Not IsEmpty(cell.Value) And IsNumeric(cell.Value) Then
Dim TP As Double
TP = cell.Value
cell.ClearContents
cell.NumberFormat = "@"
cell.Value = CStr(TP)
End If
Next cell
End Sub

You will see the result.

Method 4 – Use VBA in Excel

Steps:

• Select the data range C5:C9.
• Right-click on the sheet title.
• Select View Code from the Context menu.

You will get the VBA window.

• Enter the following code:

Sub Convert_2_Text()
Dim xRng As Range
Set xRng = Selection
    xRng.NumberFormat = "@"
End Sub

• Click the Run icon to get the output.

Excel has changed the format to text.

Download Practice Workbook

You can download the free Excel template from here and practice.