Excel VBA Codes to Format Cell as Text (3 Easy Examples)

How to Use Code in the Visual Basic Editor

In the Visual Basic for Applications window, click the Insert drop-down and select Module.

With a new module is opened, write or paste some code there and press F5 to run.

In cell C5, we have a short date that we’re going to change as text.

Sub FormatCellAsText()
Range("C5").NumberFormat = "@"
End Sub

Code Explanation:

We used the Range Object to select the cell in the worksheet that contain the input.
To format the input value as text, we need to put the Number Format value as “@”.

Similarly, by applying the same piece of code we can change different number formats to text.

Let’s say we have a Long Date in cell B6 that we want to format as text.

Sub FormatCellAsText()
Range("C6") = WorksheetFunction.Text(Range("B6"), "'0")
End Sub

By running the code with F5, you can convert the long date into a text value. We can also format a cell containing different number formats to text.

Code Explanation:

We used the Range Object to select the cells in the worksheet that contain input and output values.
The WorksheetFunction object enabled us to use the TEXT function in VBA code.
The TEXT function needs 2 arguments-
value– the input cell reference (in this example B6).
format_text- we used ” ‘ 0 ” to convert the value to text format.

Read More: How to Format Text in Excel Cell

Sub FormatCellAsText()
Range("C6").Value = Format(Range("B6").Value, "'0")
End Sub

Code Explanation:

We used the Range Object to select the cells in the worksheet that contain input and output values.
The Format function needs 2 arguments-
expression– the input cell reference (in this example B6).
format- we used ” ‘ 0 ” to convert the value to text format.

Alternative Code:

Sub FormatCellAsText()
Range("C6").Value = " ' " & Format(Range("B6").Value, "0")
End Sub

Important Notes

We added a single quote ( ‘ ) before zero to enter the number format code as ” ‘ 0 ” in the Text and Format functions argument to format a cell to a text value.
To view the code used in the sheet, click the right button on the sheet name and select the View Code option.