Skip to content

Exercise 24 of 41 · Text functions · Intermediate · 10 min

Format Numbers and Dates as Text with TEXT

Your task: In B7:B9, format the revenue, growth and date as text; in B10, join them into a caption.

Full instructions, scenario and goal

Scenario

A reporting analyst adds a one-line caption under a chart in a monthly deck. The caption must show the product, its revenue with thousands separators and cents, the growth as a percentage and the report date, and must update itself every month.

Your task

  1. In B7, show the revenue (B3) as text with thousands separators and two decimals.
  2. In B8, show the growth (B4) as a percentage with one decimal.
  3. In B9, show the report date (B5) as day/month/year with two-digit day and month.
  4. In B10, build the caption: the product, then " revenue ", the formatted revenue, the formatted growth in brackets, " as of " and the formatted date. You can reuse B7:B9.
  5. Select Check Answer when all four cells are filled.

You are done when

  • Each piece is text produced with a format code.
  • The caption has exactly the wording and spacing requested.
  • Everything is built from the cells, so a new report updates it.

What you will practise

Convert numbers, percentages and dates to formatted text with TEXT, and join them with other text using &.

The idea in 30 seconds

When you join a number to text with &, Excel uses the raw value: a percentage of 7.5% becomes 0.075 and a date becomes a serial number such as 45747. The TEXT function fixes that. TEXT(value, format) converts the value to text using a format code, the same codes you meet in the Format Cells dialog: "#,##0.00" adds thousands separators and two decimals, "0.0%" shows a percentage with one decimal, and "dd/mm/yyyy" shows a date as day, month and year.

Read the full explanation below the sheet

Spreadsheet

B7
A
B
1
2
3
4
5
6
7
8
9
10

Loading spreadsheet…

Keyboard shortcuts
Arrow keys
Move between cells
Shift + Arrow keys, or drag with the mouse
Select a range
Ctrl + Arrow keys
Jump to the edge of a block of data
Type, or F2, or double-click
Edit a cell (F2 switches arrow keys between moving and editing)
Click or arrow keys inside a formula
Insert a cell reference after =, +, (, comma and similar
F4
Cycle a reference through A1, $A$1, A$1, $A1
Enter / Tab while editing
Confirm and move down / right (Shift reverses)
Esc
Cancel the edit
Ctrl + C / X / V
Copy, cut and paste (formulas adjust like in Excel)
Delete
Clear the selected cells
Ctrl + Z / Ctrl + Y
Undo and redo
Ctrl + Enter
Check your answer
Tab when not editing
Leave the spreadsheet

Type your answer in cell B7. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.

New here? How an exercise works
  1. Read the task. Highlighted cells are yours to fill in; the rest are locked.
  2. Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
  3. Press Check answer. Stuck? Reveal hints one at a time.

The full explanation: the idea behind this exercise

When you join a number to text with &, Excel uses the raw value: a percentage of 7.5% becomes 0.075 and a date becomes a serial number such as 45747. The TEXT function fixes that. TEXT(value, format) converts the value to text using a format code, the same codes you meet in the Format Cells dialog: "#,##0.00" adds thousands separators and two decimals, "0.0%" shows a percentage with one decimal, and "dd/mm/yyyy" shows a date as day, month and year.

The result of TEXT is text, not a number, so it is for display: captions, labels, messages. Keep the original number cells for calculations. Format codes go in quotes. Remember to combine the pieces with & and to put spaces and punctuation inside quotes too. Because the caption is built from cells, it updates automatically when the report numbers change.

Skills you will practise

  • TEXT
  • Format codes
  • Concatenation
  • Dates

Topic guide: Excel Text Functions Practice: TRIM, LEFT, TEXT and More

Complete first: Clean and Combine Names with TRIM and PROPER

Common mistakes to avoid

Joining raw numbers
Excel joins the underlying value, so a percentage shows as 0.075 and a date as a serial number.
Forgetting spaces and quotes
Text between the pieces, such as " revenue ", must be in quotes and include the spaces you want to see.