How to Use the TO_TEXT, TO_DATE, and TO_DOLLARS Functions in Google Sheets to Force Value Formatting

Data imported into Google Sheets from CSV files, web forms, third-party databases, and API integrations frequently arrives with inconsistent formatting. Numbers may be stored as plain text, dates may appear as raw serial numbers, and financial amounts often lack currency standardisation. While altering visual formatting via the toolbar menu updates how data looks on screen, underlying formula calculations, lookups, and sorting operations can still fail if data types are mismatched.

Google Sheets provides a specialised family of type-forcing functions—specifically TO_TEXT, TO_DATE, and TO_DOLLARS—that programmatically coerce raw values into explicit data types and formats. Unlike cosmetic formatting, these functions ensure that your data transformations persist cleanly inside complex formula pipelines, nested arrays, and QUERY statements. In this tutorial, you will learn the syntax, practical applications, and best practices for using each of these functions.

Why Use Type Conversion Functions Instead of Format Menus?

While using Format > Number alters cell appearance, it does not always resolve formula evaluation issues:

• Programmatic Consistency: In dynamic array formulas (such as MAP, ARRAYFORMULA, or QUERY), manual toolbar formatting cannot be reliably applied to calculated virtual columns.
• Preventing Lookup Failures: VLOOKUP and XLOOKUP treat numeric 100 and text string "100" as completely distinct values. Coercing inputs eliminates “Value Not Found” errors.
• Export and API Readiness: When exporting sheets to JSON or feeding webhooks, type-forcing functions guarantee that exported records conform to strict data contracts.

1. The TO_TEXT Function

The TO_TEXT function converts any numeric, date, time, or currency value into a pure plain text string.

Syntax

=TO_TEXT(value)

• value: The cell reference or expression to be converted to plain text.

Practical Applications

Consider the following practical scenarios where TO_TEXT is essential:

Preserving Leading Zeros in Identifiers

Postal codes, telephone numbers, and employee ID numbers (such as 00452) are often stripped of leading zeros when parsed as numbers. By using TO_TEXT, Google Sheets treats the value as literal characters:

=TO_TEXT(A2)

Fixing VLOOKUP Text and Number Mismatches

If your lookup table contains product SKUs stored as text strings but user search inputs are entered as numbers, normal lookups will return #N/A. Wrap the lookup key in TO_TEXT to resolve the mismatch:

=VLOOKUP(TO_TEXT(E2), A2:C50, 2, FALSE)

2. The TO_DATE Function

Google Sheets internally stores dates as integers representing the number of days elapsed since 30 December 1899. For example, the integer 45579 corresponds to 14 October 2024. When calculations inadvertently output raw serial numbers, TO_DATE coerces the number directly into a standard calendar date.

Syntax

=TO_DATE(value)

• value: A numeric serial date, cell reference, or calculation output.

Practical Applications

Restoring Serial Integers to Dates

If an array operation or mathematical date addition (e.g., A2 + 30) displays an unformatted number like 45609, wrap the formula with TO_DATE:

=TO_DATE(A2 + 30)

Google Sheets instantly renders the result as a recognizable date according to your spreadsheet’s regional locale settings.

Standardising Dates Inside ArrayFormulas

When generating automated dates across an entire column, combine TO_DATE with ARRAYFORMULA:

=ARRAYFORMULA(IF(A2:A="", "", TO_DATE(A2:A + 14)))

This adds a two-week payment window to invoice dates while ensuring every generated row maintains correct date formatting automatically.

3. The TO_DOLLARS Function

The TO_DOLLARS function converts a numeric value into a formatted currency amount, displaying currency symbols, thousands separators, and two decimal places.

Syntax

=TO_DOLLARS(value)

• value: The numeric figure or formula expression to convert into currency format.

Practical Applications

Formatting Financial Outputs Dynamically

When calculating commission or tax figures that need to be presented with currency symbols directly inside calculated text strings, TO_DOLLARS ensures correct financial formatting:

=TO_DOLLARS(B2 * 0.15)

Concatenating Currency into Summary Sentences

If you concatenate text with numbers, such as ="Total Due: " & B2, Google Sheets drops currency formatting and outputs Total Due: 1450.5. Using TO_DOLLARS maintains the currency format:

="Total Due: " & TO_DOLLARS(B2)

The formula outputs cleanly as “Total Due: $1,450.50”.

Summary of Type Conversion Best Practices

• Locale Awareness: While TO_DOLLARS defaults to your spreadsheet’s local currency symbol, you can configure your default currency via File > Settings > Locale.
• Handling Non-Convertible Inputs: If text is passed to TO_DATE, the function returns an error. Always pair it with ISDATE or IFERROR when validating raw user inputs.
• Performance Optimization: These lightweight conversion functions execute natively in Google Sheets, making them substantially faster than complex regular expression string parsing.