Guides

Cleaning data in Google Sheets

Data from forms, exports and copied web pages is rarely tidy. Names have stray spaces and odd capitals, one column holds two facts, numbers arrive as text. Google Sheets has both menu commands and functions for cleaning, and its text-splitting and pattern functions make some jobs much shorter than in other spreadsheets.

Menu tools first

For a one-time tidy of the data itself, the menus are quickest.

• Data, Data clean-up, Trim whitespace removes leading, trailing and repeated spaces from the selected cells.
• Data, Data clean-up, Remove duplicates deletes repeated rows.
• Data, Data clean-up, Clean-up suggestions opens a side panel that points out likely problems.
• Data, Split text to columns divides a column at a separator.
• Edit, Find and replace (Ctrl and H) can search using regular expressions.

Use formulas, below, when the cleaning must happen again each time new data arrives.

1. Spaces and capitals

• =TRIM(A2)Copy
  Result: asha verma

  Removes spaces at the ends and squeezes runs of spaces to one.

• =PROPER(TRIM(A2))Copy
  Result: Asha Verma

  Then a capital for each word.

• =LOWER(B2)Copy
  Result: asha.verma@example.com

  Email addresses in small letters.

• =UPPER(C2)Copy
  Result: PB-10-1234

  Codes in capitals.

PROPER is a first pass only. It gets abbreviations and names with inner capitals wrong, so read the result.

2. Splitting

• =SPLIT(A3," ")Copy
  Result:

  RAVI

  KUMAR

  A name into two cells.

• =SPLIT(B2,"@")Copy
  Result:

  asha.verma

  example.com

  An email address at the @.

• =INDEX(SPLIT(B2,"@"),1,2)Copy
  Result: example.com

  Only the second piece.

SPLIT drops empty pieces and turns number-like pieces into numbers, which loses leading zeros. When that matters, extract instead.

3. Extracting by pattern

• =REGEXEXTRACT(C2,"^[A-Z]+")Copy
  Result: PB

  The letters at the start of the code.

• =REGEXEXTRACT(C3,"\d{4}$")Copy
  Result: 0042

  The last four digits, kept as text, zeros and all.

• =REGEXEXTRACT(D5,"\d+")Copy
  Result: 12

  The number out of "12 kg".

See REGEXEXTRACT.

4. Removing and replacing

• =SUBSTITUTE(C2,"-","")Copy
  Result: PB101234

  One exact character removed.

• =REGEXREPLACE(D2,"[^0-9.]","")Copy
  Result: 1250

  Everything except digits and dots removed.

• =REGEXREPLACE(A5,"\s+"," ")Copy
  Result: Kabir Khan

  Any run of spaces becomes one.

5. Text into numbers

• =VALUE(D3)Copy
  Result: 300

  Text digits become a number.

• =VALUE(REGEXREPLACE(D2,"[^0-9.]",""))Copy
  Result: 1250

  Strip, then convert.

• =ISNUMBER(D3)Copy
  Result: FALSE

  The test: FALSE means the cell holds text, not a number.

For a whole column, select it and choose Format, Number, Number. Sheets converts text that it can read as numbers. Values with units or stray characters need a formula.

6. Joining

• =PROPER(TRIM(A3))&" <"&LOWER(B3)&">"Copy
  Result: Ravi Kumar <ravi@example.org>

  The & sign, with cleaning inside.

• =TEXTJOIN(", ",TRUE,C2:C5)Copy
  Result: PB-10-1234, DL-08-0042, MH-12-9876, RJ-14-0007

  A range into one cell, skipping blanks.

7. Checking

• =ISEMAIL(B2)Copy
  Result: TRUE

  Has the form of an email address.

• =REGEXMATCH(C2,"^[A-Z]{2}-\d{2}-\d{4}$")Copy
  Result: TRUE

  The code has the expected shape.

• =LEN(C2)=10Copy
  Result: TRUE

  The expected length.

• =A2=TRIM(A2)Copy
  Result: FALSE

  FALSE means stray spaces are present.

Put a check in a helper column and filter for the failures.

8. Duplicates

=COUNTIF(A:A, A2)>1 flags repeated values. =UNIQUE(A2:A) lists each value once, somewhere else, leaving the data untouched. Clean spaces and case first, or "Asha" and "asha " will count as different people.

A whole column from one formula

Each cleaning formula can run down a column inside ARRAYFORMULA, so that new rows are cleaned as they arrive:

=ARRAYFORMULA(IF(A2:A="", "", PROPER(TRIM(A2:A))))

This is the usual set-up for responses from a Google Form: leave the response tab alone, and build a clean copy on another tab with one formula per column.

Making the clean version permanent

Formulas give their results in other cells. To replace the originals, copy the cleaned cells, then use Edit, Paste special, Values only over the original column, and delete the helper column. Keep a copy of the raw data on another tab until you are sure, or rely on File, Version history.

What to leave as text

Phone numbers, PIN codes, account numbers and other identifiers should stay as text. As numbers they lose leading zeros, and you never add them up. Format such columns as Plain text before pasting data in.

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.