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 join a first and last name, build an address line with concatenate, join a range with textjoin and a delimiter.
3 of 10 free · no sign-up needed · graded on the formula you write
A1 holds a first name, B1 holds a last name. Write a formula in C1 that joins them with a space using CONCATENATE.
| A | B | C | |
|---|---|---|---|
| 1 | Alex | Rivera | ? |
Use =CONCATENATE(A1," ",B1)
Right — CONCATENATE joins any number of text arguments together in order.
Expected result in C1: Alex Rivera
A1 holds a street, B1 holds a city, C1 holds a state. Write a formula in D1 that joins them as 'street, city state' using CONCATENATE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 123 Main St | Austin | TX | ? |
Use =CONCATENATE(A1,", ",B1," ",C1)
Good — CONCATENATE with literal separators mixed in builds a fully formatted address.
Expected result in D1: 123 Main St, Austin TX
A1:A3 hold three tags. Write a formula in B1 that joins them with commas using TEXTJOIN, ignoring any empty cells.
| A | B | |
|---|---|---|
| 1 | urgent | ? |
| 2 | bug | |
| 3 | frontend |
Use =TEXTJOIN(", ",TRUE,A1:A3)
Right — TEXTJOIN handles a whole range at once and can skip blanks automatically.
Expected result in B1: urgent, bug, frontend
A1:A4 hold four values, one of which is blank. Write a formula in B1 that joins the non-blank ones with a comma using TEXTJOIN.
| A | B | |
|---|---|---|
| 1 | red | ? |
| 2 | ||
| 3 | blue | |
| 4 | green |
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 customer name. Write a formula in B1 that builds 'Hello, NAME!' using CONCATENATE.
| A | B | |
|---|---|---|
| 1 | Priya | ? |
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, B1, C1 hold folder names. Write a formula in D1 that joins them with slashes using TEXTJOIN.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | reports | 2024 | q1 | ? |
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 author, B1 holds a year, C1 holds a title. Write a formula in D1 that joins them as 'Author (Year): Title' using CONCATENATE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Smith | 2023 | Excel Basics | ? |
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:A3 hold skill names. Write a formula in B1 that joins them with ' | ' as a separator using TEXTJOIN.
| A | B | |
|---|---|---|
| 1 | Excel | ? |
| 2 | SQL | |
| 3 | Python |
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 category code, B1 holds a numeric ID. Write a formula in C1 that joins them as 'CODE-ID' using CONCATENATE.
| A | B | C | |
|---|---|---|---|
| 1 | ELEC | 4021 | ? |
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:A3 hold optional note fields, with the middle one blank. Write a formula in B1 that joins the filled ones with a semicolon using TEXTJOIN.
| A | B | |
|---|---|---|
| 1 | Fragile | ? |
| 2 | ||
| 3 | Priority |
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 →