Clean Imported Amounts with TRIM, SUBSTITUTE and VALUE
Your task: In B2:B5, turn each imported text amount into a number, then total them in B7.
Full instructions, scenario and goal
Scenario
A finance assistant exports payments from an old billing system. Every amount arrives as text with irregular spaces and the word USD at the end, so SUM gives 0. They need real numbers in column B, and a total, without retyping any of them.
Your task
- In B2:B5, convert each imported amount in column A into a number: remove the spaces around it, remove the word USD, and convert the rest.
- In B7, add up the four numbers.
- Select Check Answer when you are done.
You are done when
- Each cell in B2:B5 holds a number, not text.
- The conversion reads the text in column A, so new imports work.
- The total in B7 adds the converted numbers.
What you will practise
Combine TRIM, SUBSTITUTE and VALUE to turn imported text into numbers that can be summed.
The idea in 30 seconds
Data exported from other systems often arrives as text that only looks like numbers: extra spaces, a currency word, thousands separators. Excel will not add text, so a column that looks full of amounts can give a total of 0. VALUE converts text that looks like a number into a real number; it understands commas as thousands separators, but it fails with an error (#VALUE!) if anything else is in the text.
Spreadsheet
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 B2. 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
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Data exported from other systems often arrives as text that only looks like numbers: extra spaces, a currency word, thousands separators. Excel will not add text, so a column that looks full of amounts can give a total of 0. VALUE converts text that looks like a number into a real number; it understands commas as thousands separators, but it fails with an error (#VALUE!) if anything else is in the text.
Cleaning is done in steps, and functions can be nested so that each step feeds the next. TRIM removes leading and trailing spaces. SUBSTITUTE(text, old, new) replaces every occurrence of one piece of text with another, and replacing something with "" deletes it. Wrap the cleaned text in VALUE and you have a number. Build it from the inside out: first make the text clean, then convert it.
Skills you will practise
- VALUE
- TRIM
- Substitute
- Data cleaning
- Text to number
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
- Stopping before VALUE
- SUBSTITUTE and TRIM return text. "1,250" still cannot be added until VALUE converts it.
- Forgetting the space before USD
- Replacing "USD" leaves a trailing space that VALUE tolerates, but removing " USD" keeps the cleaning exact.