Excel VBA to Convert Number to Text (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

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.


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.

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.


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo