Split text, remove duplicates, or trim whitespace

Split data into columns

You can split clearly defined data, like text separated by commas, into several columns with Google Sheets. For example, a column with "Last name, First name" can be split into 2 columns: "Last name" and "First name."

  1. On your computer, open a spreadsheet in Google Sheets.
    • If the data’s already in the sheet, select the cells you want to split.
    • If the data isn’t in the sheet yet, paste it.
  2. At the top, click Data and then Split text to columns.
  3. To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.
  4. To fix how your columns spread out after you split your text, click the menu next to "Separator" and then Detect automatically.

Note: After you paste the data, you can click Paste Paste and then Split text to columns.

Remove rows with repeat data

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the data range that you’d like to remove duplicates in.
    • Cells with identical values but different letter cases, formatting, or formulas are considered to be duplicates.
  3. At the top, click Data and then Remove duplicates.
  4. Select which columns to include, and whether or not the data has headers.
  5. Click Remove duplicates.

Note: If you select a single cell within a range, duplicates will be removed for the whole range.

Remove extra spaces

  1. On your computer, open a spreadsheet in Google Sheets.
  2. Select the data range that you’d like to remove extra leading, trailing, or excessive spaces in.
  3. At the top, click Data and then Trim whitespace.

Note: Non-breaking space, like &nbsp, will not be trimmed.

Was this article helpful?
How can we improve it?