Excel exercises
Excel exercises: Tidying text
Imported text arrives with its own casing, spacing and punctuation, and this group is the clean-up pass.
The ampersand and CONCAT join values and do not care what type they are. TEXTJOIN joins a whole range and can skip empty cells, which is the difference between a list and a list with gaps. UPPER, LOWER and PROPER normalise case, and PROPER is the one people forget until a mail merge prints names in capitals. SUBSTITUTE swaps every match, and a fourth argument picks one instance.
Four exercises at level 2. Each prompt shows the output you should see, so a tidy-up that changes a value is caught straight away.
TRIM removes the spaces at the ends and collapses any run inside to one, which is what a pasted report needs. It does nothing for a non-breaking space, the character copied tables hide. When TRIM leaves a value looking unchanged, that space is the likely reason.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 4 complete
- CONCAT and the & operator Join two cells into one string, with the space passed in as its own piece.
- TEXTJOIN: join a range and skip blanks Paid Join a column into one line and decide what happens to the blanks.
- UPPER, LOWER and PROPER Paid Normalise capitalisation with UPPER, LOWER and PROPER, and know which one a code needs.
- SUBSTITUTE: replace one piece of text Paid Replace a separator everywhere, then name a single occurrence with the fourth argument.