Fix Copy Paste Formatting in Google Sheets — Remove Spaces, Line Breaks & Hidden Characters
TL;DR: Pasting text into Google Sheets often leaves invisible extra spaces, embedded line breaks, and stray special characters that silently break formulas, lookups, and filters. Clean the text before you paste and you'll never fight it again.
Table of contents
- Why copy-paste breaks Google Sheets formatting
- The three most common culprits
- Quick fix: clean the text before you paste
- Extra spaces — the silent killer of VLOOKUP
- Line breaks trapped inside cells
- Hidden special characters from PDFs and web pages
- Pasting from Microsoft Word or Outlook
- Pasting email lists and CSV data
- Common mistakes to avoid
- FAQ
- Quick checklist
- More tools / Related links
Why copy-paste breaks Google Sheets formatting
Google Sheets looks like a simple grid, but under the hood each cell stores a raw string — and that string carries every whitespace character and control code from wherever you copied it. Unlike a word processor, Sheets does not strip or normalize whitespace. It stores exactly what it receives.
That means the moment you copy text from a website, a PDF, an email, a CRM, or a Word document and paste it into a sheet, you are also pasting:
- Leading or trailing spaces (
" Apple "instead of"Apple") - Multiple consecutive spaces between words (
"Product Name") - Non-breaking spaces (Unicode character U+00A0) from HTML content
- Embedded newlines that force a cell to grow tall and wrap
- Zero-width spaces or other invisible Unicode characters from formatted documents
None of these are visible at a glance. Yet each one can cause VLOOKUP to return #N/A, filters to miss rows, COUNTIF to under-count, or pivot tables to split one value into two separate buckets.
The three most common culprits
1. Extra whitespace from web pages. When you highlight text on a site and copy it, the browser includes the spacing the CSS uses to render the page. A heading with letter-spacing set in CSS copies those spaces right into your clipboard.
2. Line breaks from formatted documents. Word, Google Docs, and PDF viewers treat paragraphs with a hard line break (Enter key). When those characters land in a single Sheets cell, the cell wraps and the formula engine sees "Product\nName" instead of "Product Name".
3. Non-breaking spaces from HTML or Outlook. The HTML entity is a non-breaking space. It looks like a regular space in Sheets but has a different Unicode value. TRIM() in Sheets does not remove it, which is why your standard cleaning formula sometimes fails.
Quick fix: clean the text before you paste
The simplest and most reliable approach is to run the text through a cleaning tool before it touches Sheets. That way you paste clean data from the start — no formulas, no post-paste clean-up scripts.
How to do it:
- Copy the text you want to paste into Sheets.
- Open Remove Extra Spaces and paste the text there.
- The tool instantly strips leading spaces, trailing spaces, duplicate spaces, and non-breaking spaces.
- Copy the cleaned result.
- Paste into your Google Sheets cell.
For text with unwanted line breaks — for example, a paragraph you want in a single cell — use the Line Break Remover first, then follow up with the spaces tool if needed.
This two-step process handles the vast majority of copy-paste formatting problems without touching a single Sheets formula.
Extra spaces — the silent killer of VLOOKUP
If you have ever set up a VLOOKUP that you were certain was correct but got #N/A on every row, extra spaces are the most common cause. The lookup value and the table value look identical on screen but differ by one invisible space.
Scenario: You copy a list of product SKUs from an inventory report (which pads values with trailing spaces) and paste them into column A. Your lookup table in column C has clean SKUs. VLOOKUP(A2, C:D, 2, FALSE) returns #N/A.
In-sheet fix: Wrap every lookup value with TRIM() and CLEAN():
=VLOOKUP(TRIM(CLEAN(A2)), C:D, 2, FALSE)
TRIM() removes leading and trailing spaces and collapses internal multiple spaces to one. CLEAN() removes non-printable control characters. Together they handle most cases.
Before-paste fix: Paste the SKU list into Remove Extra Spaces, copy the result, paste it clean into column A. Now your plain VLOOKUP works without formula overhead.
The before-paste approach is better for large sheets because it keeps the formula simple and fast.
Line breaks trapped inside cells
A cell that shows wrapped text with multiple visual lines usually contains an embedded newline character. In Sheets, you can deliberately insert one with Ctrl + Enter (or Cmd + Enter on Mac), but they also arrive uninvited from copy-paste.
How to spot them: Click the cell. If the formula bar shows the text on multiple lines with no explicit CHAR(10) concatenation, there is an embedded newline.
Why it matters:
- Filters and sorts treat
"Smith\nJohn"and"Smith John"as different values. COUNTIFwon't count it unless you include the newline in the criteria.- Exporting to CSV wraps the cell in quotes and includes the newline, which can break the CSV reader that imports it next.
In-sheet fix:
=SUBSTITUTE(A2, CHAR(10), " ")
This replaces every embedded newline with a space. Paste-special → values only to freeze the result.
Before-paste fix: Open Line Break Remover, paste the text, and it joins the broken lines into a single continuous string. Copy the clean result and paste it into Sheets.
Hidden special characters from PDFs and web pages
PDFs are the worst offenders. Text extracted from a PDF often includes:
- Soft hyphens (U+00AD) used for line-breaking in the original layout
- Different types of dashes — en-dash (–), em-dash (—) — instead of a regular hyphen
- Ligatures like
fi(fi ligature) instead of the two lettersfi - Multiple types of quotation marks instead of straight apostrophes
Web pages add their own quirks: zero-width non-joiner (U+200C), zero-width joiner (U+200D), and word-joiner (U+2060) characters that appear in some HTML templates.
In Sheets these show up as garbled characters in some fonts, or as invisible characters that break exact-match formulas silently.
In-sheet fix:
=CLEAN(TRIM(SUBSTITUTE(A2, CHAR(160), " ")))
CHAR(160) is the non-breaking space. CLEAN() removes most control characters. This combination covers a large share of the hidden-character problem.
Before-paste fix: For the cleanest result before touching Sheets, paste the raw copied text into Remove Extra Spaces to normalize all whitespace types including non-breaking spaces, then copy the result into your cell.
Pasting from Microsoft Word or Outlook
Word and Outlook use their own internal formatting model. When you copy a bullet list or a numbered list from Word and paste it into Sheets, each bullet becomes a separate line in one cell, or each bullet lands in its own row depending on how Sheets interprets the clipboard data.
Common results:
- A pasted Word table sometimes fills multiple cells correctly, sometimes puts everything in one cell.
- An Outlook email body pastes with HTML entity characters visible (
&,<) or with extra blank lines. - Formatted text from an Outlook signature lands with the company logo described as
[image]text.
Best workflow for Word/Outlook → Sheets:
- Select the text in Word or Outlook.
- Copy it.
- Open Line Break Remover and paste. This flattens the multi-line content into a clean single block (or use the "join with space" option to keep words on one line).
- Then open Remove Extra Spaces to strip the padding that Word adds.
- Paste the clean result into Sheets.
This workflow is especially important if you are maintaining a master list in Sheets that gets updated by pasting from email reports or Word summaries.
Pasting email lists and CSV data
Email lists pasted from a newsletter platform, a CRM export, or a contact form download commonly have:
- A trailing comma or semicolon after each email address
- Extra spaces inside the address (
user @domain.com) - Mixed case that causes duplicates in Sheets filters (
[email protected]vs[email protected])
Clean approach:
- Copy the raw list.
- Use Remove Extra Spaces to strip stray whitespace.
- Use Remove Duplicate Lines to deduplicate if needed.
- Paste one address per line into a single Sheets column.
For case normalization, you can use Sheets' own =LOWER(A2) after pasting, or run the text through the Case Converter and select lowercase before pasting.
Common mistakes to avoid
Using Ctrl+V directly from a rich-text source. Always stop and ask: does this source use special formatting? If yes, clean first.
Relying on TRIM() alone. Sheets' TRIM() function only removes regular spaces (U+0020). Non-breaking spaces, zero-width spaces, and other Unicode whitespace pass through it untouched. Pair TRIM() with CLEAN() and SUBSTITUTE(x, CHAR(160), " ") for thorough cleaning.
Ignoring the character count. If a cell value looks right but formulas still fail, check the actual character count. Use Character Counter on the pasted text and compare it to what you expect. If the count is higher, hidden characters are present.
Pasting into a merged cell. Merged cells in Sheets behave unpredictably with pasted content, especially multiline content. Avoid merging cells in data ranges; use center-across-selection formatting instead for visual alignment.
Forgetting to paste as values after cleaning with formulas. If you use TRIM(CLEAN(A2)) in column B to clean column A, and then delete column A, column B breaks. Always paste-special → values only after a cleaning formula to freeze the clean result.
FAQ
Why does TRIM() not fix my non-breaking spaces?
TRIM() only targets ASCII space (U+0020). Non-breaking space is U+00A0 — a different character. Use SUBSTITUTE(A2, CHAR(160), " ") to convert it to a regular space, then apply TRIM().
I cleaned the text but VLOOKUP still fails. What else could it be? Check for different dash types (en-dash vs hyphen), different apostrophe types (curly vs straight), or mixed encoding for accented characters. The Character Counter can help you spot unexpected characters by showing you the exact count vs. what you'd expect.
How do I remove line breaks from an entire column at once?
Select the column, use Edit → Find & Replace (Ctrl+H), click "More options", check "Search using regular expressions", enter \n in the search field and a space in the replace field. This removes embedded newlines from all selected cells at once.
Can I automate this with a Google Apps Script?
Yes. A simple Apps Script onEdit trigger can run TRIM() and CLEAN() on any cell you edit. But for source data that arrives from external copy-paste, cleaning before pasting is still faster and more reliable because it handles non-breaking spaces and special Unicode that Apps Script trim() may also miss.
Does pasting as plain text (Ctrl+Shift+V) fix the problem? It removes rich-text formatting like bold and font size, but it does not remove extra spaces, line breaks, or Unicode whitespace. Those characters are in the plain text content, not in the formatting layer.
Quick checklist
- Copy the raw text from the source
- Open Remove Extra Spaces and paste — removes leading, trailing, and double spaces including non-breaking spaces
- If text has paragraph breaks you don't want: open Line Break Remover first
- Check character count with Character Counter if VLOOKUP or formulas still fail after pasting
- For email lists: use Remove Duplicate Lines to deduplicate before pasting
- In Sheets, wrap formula-cleaned values with paste-special → values to freeze clean results
- Avoid
TRIM()alone — pair it withCLEAN()andSUBSTITUTE(CHAR(160))
More tools / Related links
- Remove Extra Spaces — strip all types of extra whitespace before pasting
- Line Break Remover — flatten multi-line text to one line
- Character Counter — spot hidden characters by checking the count
- Remove Duplicate Lines — deduplicate lists before pasting into Sheets
- Case Converter — normalize email addresses and names to lowercase before pasting
- Fix Copy Paste Formatting in Microsoft Word — related guide for Word
- Fix Copy Paste Formatting in Outlook — related guide for Outlook