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.
- 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 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.
- 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. Because if numbers as stored as text then it shows a triangle-shaped green icon in the upper-left corner of the cell.
- How to Convert Date to Text YYYYMMDD (3 Quick Ways)
- Convert Date to Text Month in Excel (8 Quick Ways)
- Excel Convert Number to Text with Leading Zeros: 10 Effective Ways
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.
- 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.
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.
- 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.
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.