Practice by topic / Excel / Text Functions
Imported data is messy: extra spaces, inconsistent capitals, codes glued together. Text functions clean and reshape it without retyping anything. These 10 exercises build up step by step, starting with find the position of an @ symbol, replace a dash with a slash, find the position of a space.
3 of 10 free · no sign-up needed · graded on the formula you write
A1 holds an email address. Write a formula in A2 that returns the position of the @ symbol using FIND.
Use =FIND("@",A1)
Right — FIND returns the 1-based position of the search text within the string.
Expected result in A2: 5
A1 holds a date-like string using dashes. Write a formula in A2 that replaces all dashes with slashes using SUBSTITUTE.
| A | |
|---|---|
| 1 | 2024-03-15 |
| 2 | ? |
Use =SUBSTITUTE(A1,"-","/")
Good — SUBSTITUTE without an instance number replaces every occurrence.
Expected result in A2: 2024/03/15
A1 holds a full name. Write a formula in A2 that returns the position of the first space using FIND.
| A | |
|---|---|
| 1 | John Smith |
| 2 | ? |
Use =FIND(" ",A1)
Right — FIND locates the first occurrence of the search text.
Expected result in A2: 5
A1 holds text with the word 'the' appearing twice. Write a formula in A2 that replaces only the 2nd occurrence of 'the' with 'THE' using SUBSTITUTE.
| A | |
|---|---|
| 1 | the cat sat on the mat |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds text with spaces that need to be fully removed. Write a formula in A2 that removes every space using SUBSTITUTE.
| A | |
|---|---|
| 1 | Hello World Again |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a filename. Write a formula in A2 that returns the position of the '.' before the extension using FIND.
| A | |
|---|---|
| 1 | report.pdf |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds an old-style code starting with 'OLD-'. Write a formula in A2 that replaces 'OLD-' with 'NEW-' using SUBSTITUTE.
| A | |
|---|---|
| 1 | OLD-4021 |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds an address like 'Austin, TX'. Write a formula in A2 that returns the position of the comma using FIND.
| A | |
|---|---|
| 1 | Austin, TX |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds a phone number using dots as separators. Write a formula in A2 that replaces all dots with dashes using SUBSTITUTE.
| A | |
|---|---|
| 1 | 555.123.4567 |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A1 holds text where a word appears twice, but only the first instance should change. Write a formula in A2 that replaces just the 1st occurrence of 'red' with 'blue' using SUBSTITUTE.
| A | |
|---|---|
| 1 | red car, red bike |
| 2 | ? |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →