Excel VBA Text Function: Format Values and Cells

In Excel VBA, use the Format function—usually Format$—to turn a number or date into a formatted string; Excel’s worksheet TEXT function is a separate option. If you mean the text currently displayed in a cell, read its Range.Text property instead. The right choice depends on whether you want to create formatted text, call the worksheet function, or inspect a cell’s visible display.

What “text function” means in VBA

Excel users often search for a VBA equivalent of a worksheet formula such as =TEXT(A1,"0.00"). VBA does not use worksheet formulas as its ordinary language syntax, and its closest built-in formatting tool is Format. Given a value and a format pattern, it returns a string that represents the value in the requested style.

There are three related tools that are easy to confuse:

Format or Format$ is a VBA function that formats a value and returns text.
Application.WorksheetFunction.Text calls Excel’s worksheet TEXT function from VBA.
Range.Text is a property that reports how a cell’s value is currently displayed, including its number format and sometimes its column-width limitations.

These are not interchangeable. A formatted string is useful for a message, export, or label. A cell’s .Text property is useful when you specifically need to inspect what Excel shows. To keep a number as a number in a worksheet, usually set the cell’s value and number format rather than replacing its value with formatted text.

Format values with VBA’s Format function

Format(expression, format) takes a value and a format pattern. Format$ has the same purpose but returns a String, which makes it a natural choice when the result is definitely text. For example, with a value of 1234.5, the pattern #,##0.00 requests a thousands separator and two decimal places.

This macro demonstrates a basic numeric conversion. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub ShowFormattedAmount()
    Dim amount As Double
    Dim displayText As String

    amount = 1234.5
    ' Format the number as a string with two decimal places.
    displayText = Format$(amount, "#,##0.00")

    MsgBox displayText
End Sub

The visible result is typically 1,234.50 in a region that uses a comma for thousands and a period for decimals. The symbols used for decimal and thousands separators can follow regional settings, so a computer configured differently may display the result differently. If a value will be imported by another system, confirm which separators and date conventions that system expects.

Format dates and times

Use date and time patterns to create readable text from a VBA date value. For example, yyyy-mm-dd produces a year-month-day style date, while hh:mm represents hours and minutes. The pattern affects the string only; it does not change the underlying date value.

This macro formats the current date. The exact date in the message depends on when the macro runs. Save a copy of the workbook before running a macro, because Ctrl+Z cannot reverse VBA changes.

Sub ShowFormattedDate()
    Dim todayValue As Date
    Dim dateText As String

    todayValue = Date
    ' Create an unambiguous year-month-day string.
    dateText = Format$(todayValue, "yyyy-mm-dd")

    MsgBox dateText
End Sub

Be careful with the letter m: in date and time patterns it can mean month or minute depending on its context. For example, month is used with year or day components, while minutes are generally used alongside hour or second components. When an output is important, test the format with known dates and times rather than assuming a pattern has the intended meaning.

Use worksheet TEXT from VBA

If you specifically want Excel’s worksheet TEXT behavior, call it through the worksheet-function interface. This can be helpful when translating a worksheet formula into VBA, but it is not usually necessary for straightforward formatting; Format$ is often simpler.

With the numeric value 1234.5 and the pattern #,##0.00, this example returns a string typically displayed as 1,234.50, subject to regional settings. Save a copy before running the macro.

Sub UseWorksheetText()
    Dim resultText As String

    ' Call Excel's worksheet TEXT function from VBA.
    resultText = Application.WorksheetFunction.Text(1234.5, "#,##0.00")

    MsgBox resultText
End Sub

Both this call and Format$ produce text, not a numeric result. If the goal is simply to display a number with two decimal places in a worksheet, keep the cell numeric and apply a number format. That lets Excel continue to calculate with the value.

Read what a cell displays with Range.Text

Range.Text answers a different question: “What is Excel showing for this cell right now?” It returns a cell’s displayed text, which reflects its formatting. The property is read-only; assigning a formatted string to a cell is a separate operation.

For example, this macro reads the display of cell B2 on a worksheet named Sheet1. The result depends on that cell’s value and formatting. Save a copy before running any macro, particularly if you adapt it to change cells.

Sub ReadDisplayedCellText()
    Dim shownText As String

    ' Read the display text from one cell.
    shownText = CStr(ThisWorkbook.Worksheets("Sheet1").Range("B2").Text)

    MsgBox shownText
End Sub

A cell that contains the number 0.5 and is formatted as a percentage might return 50%, rather than the underlying numeric value. But if a column is too narrow, Excel can display hash marks such as ##### for some numeric or date values. Reading .Text can then return those visible marks rather than the full value. For calculations, use the cell’s .Value or .Value2, not its display text.

Choose based on the result you need

Decide whether the output must remain a number, become a string, or reflect the current cell display. This quick guide helps avoid a common source of bugs: converting a usable numeric value into text too early.

Use Format$ when you need a formatted string for a message, filename, report label, or text export.
Use Application.WorksheetFunction.Text when you specifically need to invoke the worksheet TEXT function from VBA.
Use Range.Text when the current rendered display of a cell is the information you need.
Use a cell’s numeric value and its NumberFormat property when the cell should remain available for arithmetic, sorting, and numeric analysis.

For example, a report heading such as “Sales for 2026-11-02” can use a formatted date string. A total in a worksheet should generally stay numeric and receive a suitable number format. Converting that total to text can make later calculations or numeric sorting behave unexpectedly.

Format codes, regional settings, and leading zeros

Format patterns specify how to represent a value; they do not rewrite the value itself. Common patterns include 0 for a required digit, # for an optional digit, 0.00 for two decimal places, 0% for a percentage, and yyyy-mm-dd for a date. The percentage pattern scales the displayed number, so a value of 0.25 formatted as 0% appears as 25%.

VBA and Excel custom number formats have substantial overlap, but they are not identical in every detail. Do not assume a format string copied from a worksheet will behave exactly the same in every VBA context. Test date patterns, literal characters, and regional separators with representative values. Named formats such as Currency can also depend on system or regional conventions.

Leading zeros deserve special care. Formatting the number 123 with 00000 can produce the text 00123, but that result is still text when created with Format$. If an identifier such as a postal code or product code must preserve leading zeros in a worksheet, decide whether it is an identifier rather than a quantity. Store identifiers as text or apply an appropriate cell number format, depending on how the data will be used.

Common problems and safer practices

A frequent mistake is using Range.Text to retrieve a value for later calculation. It returns a display representation, which may include commas, a currency symbol, a percent sign, or hash marks. Use the underlying value for calculations and format it only when presenting it.

Another mistake is expecting formatted output to remain numeric. Once Format$ returns a string, arithmetic and numeric sorting may no longer work as expected without converting the text back to a number. Keep data and presentation separate whenever practical.

Check that the source value is the expected type and that date values are valid before formatting.
Test the pattern with zero, negative, large, and boundary values where those cases matter.
Use a consistent date format for data exchanged across regions; month/day ordering can otherwise be ambiguous.
Do not use a narrow column’s visible text as a substitute for the cell’s underlying value.
Save macro-enabled workbooks as .xlsm when they need to retain VBA code, and run macros only from sources you trust.

To enter or edit a macro in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the procedure there. On Mac, the Visual Basic Editor is available through Excel’s Developer tools; the keyboard shortcut can vary with the keyboard and function-key settings. VBA macros are not supported in Excel for the web, so use the desktop app to run them.

Frequently Asked Questions

Does VBA have a TEXT function?
VBA has a Format function for producing formatted strings. You can also call the worksheet function as Application.WorksheetFunction.Text. They serve similar formatting needs, but VBA’s Format$ is often the direct choice for VBA code.

What is the difference between Format and Range.Text?
Format creates a string from a value using a pattern you provide. Range.Text reads the text Excel currently displays for a cell, taking its cell format and display conditions into account.

How do I format a number to two decimal places in VBA?
Use Format$(numberValue, "0.00"). For instance, formatting 12.3 with that pattern returns the string 12.30. Regional settings may affect the decimal separator.

How do I format a date as YYYY-MM-DD?
Use Format$(dateValue, "yyyy-mm-dd"). The result is text in year-month-day order; it does not alter the underlying date value.

Why does Range.Text return #####?
Excel may show hash marks when a formatted numeric or date value cannot fit in the column. Widen the column to inspect the display, or read the underlying cell value when you need the actual data rather than its rendered appearance.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.