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

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.


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.

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

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

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

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


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

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


<< Go Back to Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo