How to Use TEXT Function in Excel - Practical Guide

Learn to convert numbers, dates, and values into formatted text using the TEXT function. This formula lets you display data in specific formats—like currency, percentages, or custom date patterns—without changing the underlying value, essential for professional reports and dashboards.

Why This Matters

TEXT function enables precise data presentation for reports, labels, and exports while preserving original values for calculations. Critical for creating professional-looking dashboards and formatted output.

Prerequisites

•Basic understanding of cell references and formulas
•Familiarity with number formatting concepts

Step-by-Step Instructions

Open your Excel workbook

Launch Excel and open the file containing data you want to format. Identify the cell with the value needing conversion.

Click the target cell

Select an empty cell where you want the formatted result to appear, such as C2.

Enter the TEXT formula

Type the formula =TEXT(value, format_code) where value is your cell reference and format_code is the desired format in quotes, e.g., =TEXT(A2,"$#,##0.00") for currency.

Press Enter to execute

Hit Enter to confirm the formula and display the formatted result as text in your selected cell.

Copy formula down (optional)

To apply TEXT to multiple rows, click your formula cell and drag the fill handle (small square at bottom-right) downward to copy the formula to adjacent cells.

Alternative Methods

Use cell formatting instead

Right-click cell > Format Cells (Home > Format > Format Cells) to apply built-in formatting, though this doesn't convert to text—useful when calculations are still needed.

Combine with CONCATENATE

Use =CONCATENATE(TEXT(A2,"mm/dd/yyyy")," Invoice") to merge formatted values with text strings for labels.

Tips & Tricks

✓Common format codes: "$#,##0.00" for currency, "0.00%" for percentages, "mm/dd/yyyy" for dates.
✓TEXT always returns text, so results won't participate in math operations—place formulas in separate columns.
✓Use "General" format code to mimic default Excel display behavior.

Pro Tips

★Nest TEXT inside CONCATENATE for dynamic labels: =CONCATENATE("Sales: ",TEXT(B2,"$#,##0")).
★Use conditional formatting on TEXT results by referencing original cells for calculations, then format display separately.
★For regional flexibility, combine TEXT with SUBSTITUTE to replace symbols: =SUBSTITUTE(TEXT(A2,"$#,##0"),"$","€").

Troubleshooting

Formula returns #VALUE! error

Check that format_code is enclosed in quotes and uses valid Excel format codes. Verify the value argument references a cell with a numeric value, not text.

Formatted numbers won't sum in totals

TEXT returns text strings, not numbers. Keep original numeric cells for calculations; use TEXT only for display in separate cells or use VALUE() to convert back.

Date format displays wrong pattern

Excel date codes vary by locale. Use "mm/dd/yyyy" for US format or "dd/mm/yyyy" for European—adjust according to your region's standard.

Related Excel Formulas

VALUE CONCATENATE IF SUBSTITUTE DATEVALUE NUMBERVALUE

Frequently Asked Questions

What's the difference between TEXT function and cell formatting?

Cell formatting changes display only—the original value remains unchanged for calculations. TEXT converts to text permanently, so results won't sum or calculate. Use formatting for display, TEXT for creating text labels.

Can I use TEXT with conditional logic?

Yes, combine with IF: =IF(A2>100,TEXT(A2,"$#,##0.00"),TEXT(A2,"0.00%")) applies different formats based on conditions.

Where do I find format codes for TEXT function?

Format codes match Excel's built-in categories (Home > Number > Format Cells dropdown). Common codes: "#,##0" (thousands), "0.00" (decimals), "mm/dd/yyyy" (dates), "0.00%" (percentages).