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 that you want to split.
   • If the data isn’t in the sheet yet, paste it.
2. At the top, click Data Split text to columns.
3. To change which character Sheets uses to split the data, next to 'Separator' click the drop-down menu.
4. To fix how your columns spread out after you split your text, click the menu next to 'Separator'  Detect automatically.

Note: After you paste the data, you can click Paste 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 Data clean-up  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   Data cleanup  Trim whitespace.

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