Excel VBA: Format Cell as Text (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to format a cell as text using VBA code in Excel with 3 different methods. With the help of the Text and Format functions, and the Range.NumberFormat property, we can set the number format code to convert a cell value to text easily. Let’s dive into the examples and apply these techniques.

Table of Contents hide

Download Practice Workbook

3 Suitable Methods to Format Cell as Text Using VBA in Excel

1. Use of the Range.NumberFormat Property to Format Cell as Text

2. Refer the TEXT Function in a VBA Code to Format Cell as Text

3. Use of the VBA Format Function to Format Cell as Text in Excel

Notes

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Format Cell as Text.xlsm

3 Suitable Methods to Format Cell as Text Using VBA in Excel

In this section, we will demonstrate how we can format cell as text using VBA in Excel. But first, we need to know how to open the visual basic editor window in excel.

Write Code in Visual Basic Editor

Follow the steps to open the visual basic editor and write some code there.

Go to the Developer tab from the Excel Ribbon.
Click the Visual Basic option.

In the Visual Basic for Applications window, click the Insert dropdown to select the New Module

Now that a new module is opened, write some code there and press F5 to run.

1. Use of the Range.NumberFormat Property to Format Cell as Text

In this example, we’ll use the Range.NumberFormat property in our VBA code to format a cell as text. In the screenshot below, in cell C5 we have a short date that we’re going to change as text.

Now, in the visual basic editor copy and paste the following code.

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

Now press F5 to run the code.

Here we can see the short date formatted cell is changed to a text value.

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 NumberFormat value as “@”.

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

Similar Readings

2. Refer the TEXT Function in a VBA Code to Format Cell as Text

The TEXT function in Excel is a worksheet function that converts a numeric value or string to a specified format. Although it’s not a VBA function, we can use it by referring to a Worksheet Function Object to format a cell to text. Let’s say we have a Long Date in cell B6 that we want to format as text.

Put the following code in the visual basic editor to accomplish this.

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

By running the code by using F5 converted the long date into a text value. Likewise, we can 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.

3. Use of the VBA Format Function to Format Cell as Text in Excel

The Format function is one of the conversion functions in VBA Excel. This returns a formatted expression based on the format that is specified as the second argument of the function. In this example, using the following code we coveted a Long Date in cell C5 to a text.

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

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 associated with 3 different methods, click the right button on the sheet name and select the View Code option.

Conclusion

Now, we know how to format a cell as text using VBA code in Excel with 3 different examples. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.