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

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.

How to Format Cell as Text Using Excel VBA: 3 Suitable Examples

In this section, we will demonstrate how we can format cells 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 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 Number Format value as “@”.

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

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.

Read More: How to Format Text in Excel Cell

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

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

Download Practice Workbook

Conclusion

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

Related Articles

• How to Overline Text in Excel
• How to Use Formula to Find Bold Text in Excel
• How to Draw a Line Through Text in Excel
  
• Excel VBA Codes to Bold Text in a String

<< Go Back to Text Formatting | Learn Excel