Excel VBA: Format Cell as Text (3 Methods)

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.


Download Practice Workbook

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


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.

Excel VBA Format Cell as Text

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

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

Excel VBA Format Cell as Text

Now press F5 to run the code.

Excel VBA Format Cell as Text

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.

Excel VBA Format Cell as Text

Read More: How to Format Cell and Center Text with Excel VBA (5 Ways)


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.

Excel VBA Format Cell 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.

Excel VBA Format Cell as 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 (10 Approaches)


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

Excel VBA Format Cell as Text

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

Read More: How to Format Text to Capitalize First Letter in Excel (10 Ways)


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.


Related Articles

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo