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.

Practice

1. What does the Excel TEXT function do?

easy

A. It deletes all formatting from a cell.

B. It calculates the sum of numbers in a range.

C. It finds the largest number in a list.

D. It changes how numbers or dates look by turning them into styled text.

Solution

1. Step 1: Understand the purpose of TEXT function

   The TEXT function converts numbers or dates into text with a specific format you choose.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions unrelated to TEXT.

3. Final Answer:

   It changes how numbers or dates look by turning them into styled text. -> Option D

4. Quick Check:

   TEXT formats numbers/dates as text [OK]

Hint: TEXT formats numbers/dates as text with style [OK]

Common Mistakes:

• Thinking TEXT calculates or sums numbers
• Confusing TEXT with formatting removal
• Assuming TEXT changes the actual number value

2. Which of these is the correct syntax to format the number in cell A1 as currency with two decimals using TEXT?

easy

A. =TEXT(A1, "dd/mm/yyyy")

B. =TEXT(A1, "$#,##0.00")

C. =TEXT(A1, "0.00%")

D. =TEXT(A1, "General")

Solution

1. Step 1: Identify currency format pattern

   "$#,##0.00" formats numbers as currency with commas and two decimals.

2. Step 2: Check other formats

   A uses "dd/mm/yyyy" for date, B uses "0.00%" for percentage, D uses "General" for general text, so only C fits currency formatting.

3. Final Answer:

   =TEXT(A1, "$#,##0.00") -> Option B

4. Quick Check:

   Currency format uses "$#,##0.00" [OK]

Hint: Use "$#,##0.00" inside TEXT for currency [OK]

Common Mistakes:

• Using percentage or date formats for currency
• Forgetting the $ sign inside quotes
• Missing commas for thousands separator

3. Given cell A1 contains the date 2024-06-15, what will the formula =TEXT(A1, "dddd, mmmm dd, yyyy") display?

medium

A. Saturday, June 15, 2024

B. 15/06/2024

C. 06/15/2024

D. 2024-06-15

Solution

1. Step 1: Understand the format string

   "dddd" shows full weekday name, "mmmm" full month name, "dd" day with two digits, "yyyy" full year.

2. Step 2: Apply to date 2024-06-15

   June 15, 2024 is a Saturday, so the output is "Saturday, June 15, 2024".

3. Final Answer:

   Saturday, June 15, 2024 -> Option A

4. Quick Check:

   Format "dddd, mmmm dd, yyyy" = Saturday, June 15, 2024 [OK]

Hint: dddd = weekday, mmmm = full month name [OK]

Common Mistakes:

• Confusing day/month order
• Using short month/day codes
• Ignoring weekday name format

4. You want to format the number 0.85 in cell B2 as a percentage with one decimal place using TEXT. Which formula?

medium

A. =TEXT(B2*100, "0.0%")

B. =TEXT(B2, "0.0")

C. =TEXT(B2, "0.0%")

D. =TEXT(B2, "0.0%%")

Solution

1. Step 1: Understand percentage formatting in TEXT

   In TEXT, the format "0.0%" automatically multiplies the number by 100 and adds the % sign, so TEXT(0.85, "0.0%") displays "85.0%".

2. Step 2: Compare other options

   A multiplies by 100 first (85), then "0.0%" makes "8500.0%"; B shows "0.9"; D with "0.0%%" shows "0.9%" (%% is literal % without scaling).

3. Final Answer:

   =TEXT(B2, "0.0%") -> Option C

4. Quick Check:

   TEXT(0.85, "0.0%") = "85.0%" [OK]

Hint: Use "0.0%" to convert decimal to formatted percentage [OK]

Common Mistakes:

• Multiplying by 100 manually (over-formats to 8500%)
• Omitting % (shows plain decimal "0.9")
• Using %% (prevents scaling, shows "0.9%")

5. You have a list of prices in column A and want to create a new column B that shows prices formatted as dollars with commas and two decimals, but also adds the text " each" after the price. Which formula in B1 correctly does this?

hard

A. =TEXT(A1, "$#,##0.00") & " each"

B. =TEXT(A1 & " each", "$#,##0.00")

C. =TEXT(A1, "$#,##0.00 each")

D. =TEXT(A1, "$#,##0.00") + " each"

Solution

1. Step 1: Format price as currency text

   Use TEXT(A1, "$#,##0.00") to format number with $ sign, commas, and two decimals.

2. Step 2: Add text " each" after formatted price

   Concatenate with & operator: TEXT(...) & " each" adds the text after the formatted number.

3. Final Answer:

   =TEXT(A1, "$#,##0.00") & " each" -> Option A

4. Quick Check:

   Concatenate formatted text with & and string [OK]

Hint: Use & to join formatted text and extra words [OK]

Common Mistakes:

• Trying to format number and text together inside TEXT
• Using + instead of & for text concatenation
• Putting text inside format string incorrectly