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

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

## 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, 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

Advanced Excel Exercises with Solutions PDF