Course outline / Text functions
Lesson 32 of 75 · Intermediate · 4 exercises
Text and numbers together
Turn numbers into neat text with TEXT, and text back into numbers with VALUE.
What you'll practise
- Format a number as text
- Put a number in a sentence
- Turn text into a number with VALUE
- Pull a number out of a code
Format a number as text
TEXT turns a number into text in the format you choose. It uses format codes:
0.00 two decimals, #,##0 thousands separators, 0% a percentage.
In cell E2, use TEXT to show B2 with thousands separators and two decimals: "#,##0.00".
Put a number in a sentence
Joining a number to text with & loses its formatting: ="Total: "&B2 gives Total: 1250.5. Wrap the number in TEXT to control how it looks.
In cell E2, join "Total: " with B2 formatted as "$#,##0".
Turn text into a number with VALUE
Numbers imported from other systems sometimes arrive as text, so they won't add up. VALUE converts them into real numbers.
In cell E2, convert the text in C2 into a number with VALUE, then multiply it by 2.
Pull a number out of a code
Combine VALUE with RIGHT to turn the digits at the end of a code into a number you can calculate with.
In cell E2, take the last 4 characters of A2 with RIGHT, and turn them into a number with VALUE.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.