TEXT function for formatting in Excel - Step-by-Step Guide

Introduction

The TEXT function helps you change how numbers, dates, or times look in your spreadsheet. It turns raw numbers into nicely formatted text, like showing a date as 'January 1, 2024' or a number as '$1,000.00'. This makes your data easier to read and share.

When you want to show a date in a friendly format like 'Monday, Jan 1'

When you need to display a number with currency symbols and two decimals

When you want to show a time as hours and minutes only

When combining numbers and text in one cell and want the number formatted

When preparing reports that require specific number or date formats

Steps

Step 1: Click

- a blank cell where you want the formatted result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula starts appearing in the cell

💡 Start with =TEXT(

Step 3: Enter

- the value or cell reference you want to format

The formula now includes the value, for example, A1 or a number like 1234.56

Step 4: Type

- the format code inside double quotes after a comma

The formula now looks like =TEXT(A1, "format_code")

💡 Use format codes like "mm/dd/yyyy" for dates or "$#,##0.00" for currency

Step 5: Press

- Enter key

The cell shows the value formatted as text according to your format code

Before vs After

Before

Cell A1 contains the number 1234.56

After

Using =TEXT(A1, "$#,##0.00") shows "$1,234.56" as text in the formula cell

Settings Reference

Format_text argument

📍 Inside the TEXT function formula

Defines how the number or date is displayed as text

Default: No default, you must specify a format

Common Mistakes

Not putting the format code inside double quotes

Excel will show an error because it expects a text string for the format

Always enclose the format code in double quotes, like "mm/dd/yyyy"

Using TEXT function on a cell with text instead of a number or date

TEXT only formats numbers or dates; text input will not change

Use TEXT only on numbers or dates, not on plain text

Summary

The TEXT function changes how numbers, dates, or times look by turning them into formatted text.

You must provide a format code inside double quotes to tell Excel how to display the value.

The result is text, so you cannot use it for calculations without converting back.