Excel VBA to Convert Number to Text (4 Examples)

We can easily change the format of numbers to text using commands in Excel. But it’s not so feasible in all cases. In some particular cases, VBA can do the task smartly and quickly. In this article, you will learn 4 easy VBA macros to convert number to text in Excel.

Excel VBA to Convert Number to Text (4 Examples)

Let’s get introduced to our dataset first. It represents the published article numbers of some writers of a content publishing site named ‘ExcelDemy’. Here the numbers are in number format. We’ll convert it to text format using VBA.

1. Use VBA Cstr Function to Convert Number to Text in Excel

In this method, we’ll learn how to convert any number to text and get the output in the Immediate Window. For that, we’ll use the VBA Cstr function here and will not use the data from the dataset. Let’s start to see how to do it.

Steps:

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

A VBA window will open up.

• Now type the following codes in it-

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

• Then 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.
• Finally, just press the Run tab.

Now have a look that, the Print function changed the format of text ‘ExcelDemy’ to text format and ‘2’ to Number format but the Cstr function has converted the format of 2 to text format. You can be sure by checking the alignment. We know that the text format remains aligned to the left and the number format remains aligned to right in Excel.

2. Apply VBA Cstr Function with Selected Range to Convert Number to Text

Here, we’ll again use the Cstr function with a specified range in the codes. The range is C5:C9.

Steps:

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

• After appearing the VBA window write the following codes-

    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

• After that, click the Run icon to run the codes, and soon after a Macro dialog box will open up.

• Then click the macro name and press Run.

Now see that the numbers are converted and stored as text. If numbers as stored as text then it shows a triangle-shaped green icon in the upper-left corner of the cell.

3. Use VBA Cstr Function with Selection to Convert Number to Text in Excel

In this method, at first, we’ll select the range and then we’ll convert it to text using VBA.

Steps:

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

Soon after, you will get the VBA window.

• Write 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

Now see the output, we are done.

4. Change Number Format to Text Using VBA in Excel

In our last method, we’ll just change the format to text, and won’t store it as text.

Steps:

• Select the data range.
• Open the VBA window like the previous methods.

Then type the following codes-

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

• Finally, just click the Run icon to get the output.

Now see that Excel has changed the format to text.

Download Practice Workbook

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

Conclusion

I hope the procedures described above will be good enough to convert numbers to text using Excel VBA. Feel free to ask any question in the comment section and please give me feedback.