Cleaning Up Data Pasted from Excel or Google Sheets

How a table copied from Excel or Google Sheets becomes tabs and line breaks, and how to clean NBSP, stray spaces and empty rows or turn tabs into commas.

When you copy a table from a spreadsheet and paste it into a messenger, an email or an input field in another system, the layout gets distorted or invisible spaces come along after the values. Once you know how the copied text is structured, cleaning it up becomes easy.

The structure of a copied table

When Excel and Google Sheets copy a range of cells as text, they put a tab between cells in the same row and a line break between rows. In other words, one line is one row, and the tabs within a line are the column boundaries.

Name	Dept	Email
Minsu Kim 	Sales	minsu@example.com
		
Jieun Lee	Dev	jieun@example.com

The example above contains three common problems: a space after Minsu Kim, an empty row containing only tabs, and a space at the end of an email address. If the values were copied from the web and pasted into the sheet, the spaces are often NBSP (U+00A0) rather than ordinary spaces.

When cells contain line breaks

If a value has a line break inside the cell, entered with Alt+Enter or similar, that cell is usually wrapped in double quotes in the copied text and the line break inside it is copied as is. Every operation in this tool works line by line, so such a cell is treated as if the row were split into two lines. For data with line breaks inside cells, it is safer to remove them in the sheet first. In Excel, a formula such as =SUBSTITUTE(A2,CHAR(10)," ") replaces line breaks inside a cell with spaces.

A cleanup order that preserves the tab structure

In table data, tabs are the column separators, so be careful with operations that touch tabs.

Clean invisible characters and quotes — 'Special spaces (NBSP etc.) → regular space' turns NBSP into ordinary spaces. Zero-width characters and the BOM are removed as well.
Find and replace — turn on regex and replace [ ]*\t[ ]* with \t to remove the spaces around cell boundaries.
Add another find and replace with the search text ^[ ]+|[ ]+$ and leave the replacement empty. This removes only the spaces at the very start and end of each line.
Remove blank lines — 'Remove all blank lines' deletes the empty rows. Lines containing only tabs also count as blank.

There is a reason steps 2–3 are not replaced by a single Trim leading and trailing spaces step. Trimming removes tabs as well as spaces, so in rows whose first or last cell is empty, the columns shift by one. Likewise, when using Collapse repeated spaces and tabs, you must turn off 'Merge tabs into spaces too' so tabs are not turned into spaces.

Name	Dept	Email
Minsu Kim	Sales	minsu@example.com
Jieun Lee	Dev	jieun@example.com

Turning tabs into commas

If you need to send the cleaned table in comma-separated form, keep find and replace in plain mode with 'Interpret \n \t as line break and tab' turned on, and replace \t with , .

Name, Dept, Email
Minsu Kim, Sales, minsu@example.com
Jieun Lee, Dev, jieun@example.com

This result is meant for people to read. If a value contains a comma (for example, Austin, TX), it cannot be told apart from a column boundary, so if you intend to load the data into another program as CSV, the spreadsheet's CSV export is the accurate way.

If you only need one column as a list

If you only need the email column, the simplest way is to select just that column in the sheet and copy it. After copying, remove duplicates with Remove duplicate lines and sort with Sort to get a mailing list right away.

Extracting a specific column

If you cannot reopen the sheet and only have the copied text, you can keep just the column you want using the regex mode of find and replace. [^\t]* means 'a run of characters that are not tabs', in other words, one cell.

First column only: find ^([^\t]*)\t.*$, replace with $1
Second column only: find ^[^\t]*\t([^\t]*).*$, replace with $1

Name
Minsu Kim
Jieun Lee

For the third column, add one more [^\t]*\t at the front. Lines with only one column (lines without a tab) do not match the pattern and stay as they are, so check the result. If you do not need the header row, delete the first line of the result.

Header rows and sorting

If you sort the whole table, the header row (Name Dept Email) gets mixed in with the other rows. Sorting compares entire lines, which has the same effect as sorting by the first column, but the header may end up in the middle. It is safer to cut the header row out and paste it back on top of the result, or to sort in the sheet and use this tool only to clean up spaces and empty rows. For the same reason, removing duplicate lines only removes rows that are identical in their entirety.

When numbers are not recognized as numbers

If you paste the cleaned values back into the sheet and the numbers are left-aligned and the total is 0, full-width digits (１２３) or leftover NBSP may be the cause. Convert full-width characters with 'Full-width → half-width' in Full-width ↔ half-width.

Checklist

If cells contain line breaks, remove them in the sheet first.
To preserve the table structure, avoid operations that also remove tabs.
Turn NBSP into regular spaces before cleaning up spaces.
After converting to commas, check for commas inside values.

Last updated: 2026-09-23