The dataset below represents the published article numbers of some writers on a content publishing site named ExcelDemy. The numbers are in number format. We’ll convert them to text format using VBA.
Method 1 – Use VBA Cstr Function
Steps:
- Right-click on the sheet title.
- Select View Code from the Context menu.
A VBA window will open up.
- Enter the following code:
Sub ConvertTo_Text()
Debug.Print "ExcelDemy"
Debug.Print 2
Debug.Print CStr(2)
End Sub
- 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.
- Press the Run tab.
The Print function changed the format of text ‘ExcelDemy’ to text format and ‘2’ to Number format but the Cstr function converted the format of 2 to text format. The text format remains aligned to the left, and the number format remains aligned to the right in Excel.
Method 2 – Apply VBA Cstr Function with Selected Range
Steps:
- Select View Code from the Context menu after right-clicking on the sheet title.
- The VBA window will open.
- Enter the following code:
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
- Click the Run icon to run the codes, and a Macro dialog box will open.
- Click the macro name and press Run.
The numbers are converted and stored as text. If the numbers are stored as text, a triangle-shaped green icon appears in the upper-left corner of the cell.
Method 3 – Use VBA Cstr Function with Selection
Steps:
- Select the data range C5:C9.
- Right-click on the sheet title.
- Select View Code from the Context menu.
You will get the VBA window.
- Enter 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
You will see the result.
Method 4 – Use VBA in Excel
Steps:
- Select the data range C5:C9.
- Right-click on the sheet title.
- Select View Code from the Context menu.
You will get the VBA window.
- Enter the following code:
Sub Convert_2_Text()
Dim xRng As Range
Set xRng = Selection
xRng.NumberFormat = "@"
End Sub
- Click the Run icon to get the output.
Excel has changed the format to text.
Download Practice Workbook
You can download the free Excel template from here and practice.
Get FREE Advanced Excel Exercises with Solutions!