Format numbers in text with the TEXT function
IntermediateThe TEXT function converts a number into a formatted text string. You can combine it with other text using the & operator to build readable labels:
="Q1 result: "&TEXT(85000, "$#,##0")
This returns "Q1 result: $85,000". The format code "$#,##0" adds a dollar sign and thousands separators. A code like "0.0%" multiplies the value by 100 and appends a percent sign. For example, 0.1 becomes "10%".
Your task
The table has financial metrics. Each row has a metric name in column A, a raw value in column B, and the format code to use in column C.
Fill in column D with a report label for each row. Each label should combine the metric name, a colon and space, and the formatted value.
For example, the Revenue row should produce: Revenue: $1,234,567
Use the format codes already in column C rather than typing them directly into your formula.
Note: Some format codes in column C may show the error "Number stored as text". You can ignore that for this exercise, it is a quirk of the spreadsheet editor.
Need some help?
Hint 1
The & operator joins values together, but numbers joined directly with & lose their formatting ("Revenue: 1234567" rather than "Revenue: $1,234,567"). TEXT is what converts the raw number into a formatted string before & joins it into the label.
Hint 2
Use the & operator to join text pieces together. TEXT takes exactly two arguments: the cell holding the raw number, and the cell holding the format code. Each row's formula can reference columns B and C directly.
Related function(s)
Format numbers in text with the TEXT function
IntermediateThe TEXT function converts a number into a formatted text string. You can combine it with other text using the & operator to build readable labels:
="Q1 result: "&TEXT(85000, "$#,##0")
This returns "Q1 result: $85,000". The format code "$#,##0" adds a dollar sign and thousands separators. A code like "0.0%" multiplies the value by 100 and appends a percent sign. For example, 0.1 becomes "10%".
Your task
The table has financial metrics. Each row has a metric name in column A, a raw value in column B, and the format code to use in column C.
Fill in column D with a report label for each row. Each label should combine the metric name, a colon and space, and the formatted value.
For example, the Revenue row should produce: Revenue: $1,234,567
Use the format codes already in column C rather than typing them directly into your formula.
Note: Some format codes in column C may show the error "Number stored as text". You can ignore that for this exercise, it is a quirk of the spreadsheet editor.
Need some help?
Hint 1
The & operator joins values together, but numbers joined directly with & lose their formatting ("Revenue: 1234567" rather than "Revenue: $1,234,567"). TEXT is what converts the raw number into a formatted string before & joins it into the label.
Hint 2
Use the & operator to join text pieces together. TEXT takes exactly two arguments: the cell holding the raw number, and the cell holding the format code. Each row's formula can reference columns B and C directly.