No login. No upload. Just focused text utilities.

Fix Copy Paste Formatting in Google Sheets — Remove Spaces, Line Breaks & Hidden Characters

2026-10-29 · 11 min read

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

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:

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:

  1. Copy the text you want to paste into Sheets.
  2. Open Remove Extra Spaces and paste the text there.
  3. The tool instantly strips leading spaces, trailing spaces, duplicate spaces, and non-breaking spaces.
  4. Copy the cleaned result.
  5. 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:

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:

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:

Best workflow for Word/Outlook → Sheets:

  1. Select the text in Word or Outlook.
  2. Copy it.
  3. 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).
  4. Then open Remove Extra Spaces to strip the padding that Word adds.
  5. 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:

Clean approach:

  1. Copy the raw list.
  2. Use Remove Extra Spaces to strip stray whitespace.
  3. Use Remove Duplicate Lines to deduplicate if needed.
  4. 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


More tools / Related links



Related cluster (planned topics)

About

JustTextTool is a text utility project focused on clean formatting, developer workflows, and practical writing improvements.