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.
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.
Read More: How to Format Cell and Center Text with Excel VBA (5 Ways)
Similar Readings
- How to Change Font Size of the Whole Sheet with Excel VBA
- How to Write 001 in Excel (11 Effective Methods)
- Text Alignment with VBA Command Button in Excel (5 Methods)
- How to Add Text after Number with Custom Format in Excel (4 Ways)
- How to Capitalize First Letter of Each Word in Excel (4 Ways)
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 (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
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
- How to Add Leading Zeros in Excel Text Format (10 Ways)
- How to Capitalize the First Letter in Excel (3 Methods)
- [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
- How to Change Lowercase to Uppercase in Excel (6 Methods)
- Use VBA Code to Change Font Color in Excel (3 Methods)
- How to Capitalize Each Word in Excel (7 Ways)