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.


Download Practice Workbook

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


4 Examples to Convert Number to Text with Excel VBA

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.


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.

Use VBA Cstr Function to Convert Number to Text in Excel

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

Use VBA Cstr Function to Convert Number to Text in Excel

  • Select the macro name as specified in the codes.
  • Finally, just press the Run tab.

Use VBA Cstr Function to Convert Number to Text in Excel

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 left and the number format remains aligned to right in Excel.

Read More: How to Convert Text to Numbers 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.

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

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

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

  • Then click the macro name and press Run.

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

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

Read More: Excel VBA: Convert Number to Text with Format (A Total Guide)


Similar Readings:


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.

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

  • 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

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

Now see the output, we are done.

Related Content: How to Convert Number to Text without Scientific Notation in Excel


4. Change Number Format to Text Using VBA in Excel

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

Steps:

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

Change Number Format to Text Using VBA in Excel

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.

Change Number Format to Text Using VBA in Excel

Now see that Excel has changed the format to text.

Read More: How to Convert Number to Text with 2 Decimal Places in Excel (5 Ways)


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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo