To format a cell as text in Excel VBA, set the cell’s NumberFormat property to "@", as in Range("A1").NumberFormat = "@". This is the direct, reliable method; you can also use Range("A1").NumberFormat = "Text" (which is equivalent) or apply the Text format to an entire column before entering data. The "@" code tells Excel to treat the cell’s contents as a text string, regardless of whether you type numbers, dates, or formulas.
Why the Text Format Matters in Excel
Excel’s default behavior is to guess the data type of anything you type. Enter 00123 and it becomes 123; type a long number like a credit card or product ID and it turns into scientific notation (e.g., 1.23E+15). The Text format prevents this auto-conversion, which is essential for:
- Leading zeros in ZIP codes, employee IDs, or part numbers.
- Long numeric strings that exceed 15 digits (Excel’s precision limit) — like serial numbers or bank account numbers.
- Mixed data such as phone numbers with dashes or alphanumeric codes.
- Values you want to display exactly as typed, without rounding or formatting changes.
Once a cell is formatted as Text, anything you enter is stored as a string. However, note that if you change the format to Text after a number is already in the cell, Excel may not re-interpret the existing value — you often need to re-enter the data or use Text to Columns to force conversion.
How the NumberFormat Property Works in VBA
The NumberFormat property uses Excel’s format codes. The code "@" is the universal placeholder for text. When you set it, the cell’s display and storage behavior becomes text-like. Here is the simplest working example:
Sub FormatAsText()
' Formats cell A1 as text
Range("A1").NumberFormat = "@"
End Sub
You can also apply it to a range or entire column:
Sub FormatColumnAsText()
' Formats entire column A as text
Columns("A").NumberFormat = "@"
' Formats A1:A100 as text
Range("A1:A100").NumberFormat = "@"
End Sub
Alternatively, NumberFormat = "Text" works identically because Excel internally maps the word “Text” to the "@" code. Use whichever reads clearer in your code.
Using the Range.Text Property vs. NumberFormat
A common confusion is between NumberFormat (which sets the display format) and Range.Text (which reads the displayed value). If you want to read a cell as text without changing its format, you can use Range("A1").Text — but this returns the formatted display, not necessarily the underlying value. For example, if A1 contains 1234 with a custom format #,##0, .Text returns "1,234". To get the raw value as a string, use CStr(Range("A1").Value) or Range("A1").Value2 with explicit conversion.
When to Set Text Format Before Entering Data
The most reliable approach is to format the cells as Text before writing values into them. If you write a number first, then change the format, Excel keeps the numeric type internally. Example:
Sub WriteLeadingZeros()
' Set format first, then enter value
Range("B2").NumberFormat = "@"
Range("B2").Value = "00123" ' Stays as text
' Compare: format after entering
Range("C2").Value = "00123" ' Becomes numeric 123
Range("C2").NumberFormat = "@" ' Too late, now shows 123
End Sub
If you must fix existing data, use the Text to Columns wizard (Data > Text to Columns > Delimited > Next > Next > Text) or a VBA loop that converts each cell via Range.Value = CStr(Range.Value) after setting the format.
Common Problems and Misconceptions
Problem: The format doesn’t stick. If you set NumberFormat = "@" but then use .Value = 123, Excel may still store it as a number because you’re assigning a numeric type. Always assign a string: .Value = "123" or .Value = CStr(123).
Misconception: Formatting as text makes formulas not work. Text-formatted cells will not calculate formulas — if you enter =A1+B1 into a text-formatted cell, it displays the formula as text, not the result. That is by design. Use text format only for values that should not be calculated.
Problem: Numbers with decimals lose precision. If you format a cell as text and type 1.23456789012345, it stays as text, but if you later convert it to number, you may get rounding. Text format is not for numeric calculations.
Problem: Excel still shows green triangles. The green triangle warning appears when a text cell looks like a number. To suppress it, you can set Application.ErrorCheckingOptions.NumberAsText = False (but this is global). Better to ignore the triangle or use Range("A1").Errors.Item(xlNumberAsText).Ignore = True.
Alternative Methods: Paste Special and Worksheet Events
Besides NumberFormat, you can format cells as text via:
- Paste Special: Copy a blank cell, select your range, right-click > Paste Special > Values and number formats, then apply Text format manually (Home > Number Format > Text).
- Worksheet Change event: Automatically format new entries as text when a user types in a specific column:
Private Sub Worksheet_Change(ByVal Target As Range)
' If user types in column A, format as text
If Not Intersect(Target, Columns("A")) Is Nothing Then
Target.NumberFormat = "@"
Target.Value = Target.Text ' Re-enter as text
End If
End Sub
This event approach is useful for user-facing sheets where you can’t control data entry beforehand.
Performance and Best Practices
Setting NumberFormat on a large range (e.g., Columns("A:A")) is fast, but doing it cell-by-cell in a loop is slow. Always apply to the entire range at once. If you need to format thousands of rows, use:
Range("A1:A10000").NumberFormat = "@"
This is a single operation. Also, remember that changing formats can affect existing data’s alignment (text defaults to left-aligned). You may want to set horizontal alignment explicitly if needed.
Frequently Asked Questions
How do I convert a column to text in VBA?
Select the whole column and set its NumberFormat: Columns("C").NumberFormat = "@". Then, if the column already contains numbers, you must re-enter them as text (e.g., loop through and set cell.Value = CStr(cell.Value)).
Why does my leading zero disappear even after formatting as text?
Because you likely formatted the cell after typing the number. Excel stores the numeric value; formatting later doesn’t re-add the zero. Delete the cell contents, format as text, then retype the value with the leading zero.
Does formatting as text affect sorting or filtering?
Yes. Text-formatted numbers sort lexicographically, so 10 comes before 2. If you need numeric sorting, keep the column numeric or use a helper column with VALUE().
Can I format a cell as text but still use it in a formula?
You can reference it in formulas, but the text value will not be treated as a number unless you convert it inside the formula, e.g., =VALUE(A1)+1. If you want the cell to display as text but calculate, use a custom format like "0" instead.
What is the difference between NumberFormat and NumberFormatLocal?NumberFormat uses English-US format codes; NumberFormatLocal uses the user’s regional settings. For text, "@" works in both, but for date or currency codes, use NumberFormatLocal if your workbook is shared across locales.
How do I remove text formatting and return to General?
Set Range("A1").NumberFormat = "General". This will not convert existing text back to numbers automatically; you’ll need to re-enter or use Value conversion.
Related
- Excel Template Job Schedule: How to Build One
- Excel Template for Weekly Employee Schedule: How to Build One
- Vacation Budget Excel Template: How to Plan & Track
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Quit UserForm: How to Hide or Unload the FormTo quit a VBA UserForm, use Unload Me in a button or event procedure on the form; this…
- Google Sheets Macro Recorder: How to Record and RunYes. Google Sheets has a built-in macro recorder: in a spreadsheet open in a desktop browser, choose Extensions…
- Excel VBA Format Cell Bold: The Complete GuideTo bold a cell in Excel VBA, use the Font.Bold property: Range("A1").Font.Bold = True. This single line of…
- Excel VBA Copy Until Last Row: Reliable Code ExamplesTo copy a range in Excel VBA through the last populated row, find that row with End(xlUp), build…
- VBA Copy Paste Mode: How to Clear and Control ItIn Excel VBA, copy/paste mode is Excel’s temporary state after a cell or range has been copied or…
- Refresh Pivot Table Using VBA: Code and TipsTo refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method; to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.