Sheet Happens
Intermediate

Cleaning up text: TRIM, PROPER, LEFT and FIND

9 minutes · 4 steps · free, no sign-up

Data rarely arrives clean: stray spaces, odd capitals, details stuck together. Text functions let you tidy and split it with formulas instead of retyping.

In this lesson you will
  • Remove extra spaces with TRIM
  • Fix capitals with PROPER
  • Cut text with LEFT and find a character with FIND
  • Join text together with &
A1 fx

Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.

Quick referenceText functions

=TRIM(text)               remove extra spaces
=PROPER(text)             Title Case
=LEFT(text, count)        first characters
=FIND("@", text)          position of a character
=A2&" "&B2                join text with &

The key ideasSummary: Cleaning up text: TRIM, PROPER, LEFT and FIND

TRIM removes spaces at the start and end of text and squashes repeated spaces in the middle. PROPER capitalises the first letter of every word and lowercases the rest. Chain them by pointing one formula at the result of another.

LEFT returns a number of characters from the start of the text. Combined with FIND, which returns the position of a character, you can cut text at any marker: =LEFT(D2,FIND("@",D2)-1) returns everything before the @. The & operator joins pieces of text together. RIGHT, MID and SUBSTITUTE work in the same spirit.

Watch out forCommon mistakes

  • Treating extracted digits as numbers. LEFT, RIGHT and MID return text, even when it looks like a number. Wrap them in VALUE() if you need to add them up.
  • Case-sensitive searching. FIND distinguishes capitals, so FIND("a","Ada") finds the second letter. Use SEARCH when case should not matter.
  • Spaces that TRIM cannot see. Text pasted from web pages often contains non-breaking spaces, which TRIM leaves behind.

Next stepTry it in the real Google Sheets

For a one-off clean-up you can skip formulas: select the column and choose Data → Data cleanup → Trim whitespace. For non-breaking spaces, use =SUBSTITUTE(A2,CHAR(160)," ") and then TRIM. The SPLIT function can split a whole column at an @ or comma in a single step, which is covered at the Adept level.

Keep going

More Intermediate lessons

Splitting and joining text Pull a first and last name out of one cell, and join pieces of text back together. Working with dates Build dates with DATE, take them apart with YEAR, MONTH and DAY, and show them the way you want. Date calculations Work out days between dates, whole months, working days, and dates a few months away. Looking things up with VLOOKUP Look up a name or price from a table, and show a friendly message when it is missing. Tiered lookups Find which band a number falls into, such as a grade, a tax band or a discount tier. Rounding and remainders Round money, work out how many boxes you need, and find what is left over.