Remove Spaces in Excel

Strip leading, trailing, double, and invisible spaces from Excel cells. Every method — TRIM, SUBSTITUTE, Find & Replace, Power Query — explained with step-by-step examples.

★★★★★4.9·Free Guide

Quick Reference: Excel Space Removal Formulas

=TRIM(A1)

Remove leading, trailing & extra spaces

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

Remove non-breaking spaces (web data)

=SUBSTITUTE(A1," ","")

Remove ALL spaces including between words

=TRIM(CLEAN(A1))

Remove spaces + non-printable characters

=VALUE(TRIM(A1))

Remove spaces and convert text to number

Ctrl+H → space → empty

Find & Replace all spaces (no formula)

Paste text to clean before copying into Excel — strips invisible characters, non-breaking spaces, and formatting artifacts

Paste your text

0 words0 hidden

Clean result — ready to paste into Excel

Other Text Utility Tools

Text to HEX Converter

Convert text characters into hexadecimal representation. Supports UTF-8 encoding with uppercase/lowercase and spacing options.

Open Tool →

Format Remover

Strip markdown formatting, curly quotes, em dashes, and invisible characters from AI and rich-text output in one click.

Open Tool →

UTF-8 Decode

Decode UTF-8 byte values back into readable text.

Open Tool →

Text Cleaner

Clean text from any source — remove invisible characters, strip markdown, fix spacing, and produce plain, publish-ready output in one click.

Open Tool →

Strip HTML

Remove HTML tags and return clean plain text.

Open Tool →

Text to HTML Entities Converter

Encode text into HTML entities for safe markup.

Open Tool →

URL Encoder / Decoder

Encode or decode URLs, query strings, and text fragments.

Open Tool →

Clean Paste

Clean text before pasting — remove invisible Unicode, markdown, and formatting artifacts so every paste is a clean paste.

Open Tool →

Remove Spaces in Excel — The Complete Guide

Remove spaces in Excel is one of the most common data cleaning tasks in any spreadsheet workflow. Whether you are dealing with leading spaces before text, trailing spaces after values, double spaces between words, or invisible non-breaking spaces from web-copied data, extra spaces in Excel cells cause real problems: VLOOKUP returns errors, SUM returns zero for number cells, and data that looks identical fails every comparison. This guide covers every method to remove spaces in Excel — from the TRIM function for quick fixes to Power Query for automated recurring imports.

Extra spaces in Excel are not always visible. A cell containing "Smith " and a cell containing "Smith" look identical on screen, but Excel treats them as different values. A number stored as " 1,234" (with a leading space) looks like a number but will not sum — Excel sees it as text. Knowing how to strip spaces in Excel reliably is a fundamental skill for anyone working with data from external sources, AI tools, or copy-paste operations.

How to Remove Spaces in Excel Using TRIM

The TRIM function is Excel's built-in solution for removing spaces from text. It strips all leading spaces (before the first character), all trailing spaces (after the last character), and reduces multiple consecutive spaces between words to a single space.

TRIM Syntax

=TRIM(text) — where text is a cell reference (like A1) or a text string in quotes.

How to Use TRIM to Remove Spaces in Excel

Insert a blank column next to the column with spaces (right-click the column header > Insert).
In the first cell of the blank column, type =TRIM(A1) (replacing A1 with your first data cell).
Press Enter to see the trimmed result.
Copy the formula down the entire column by double-clicking the fill handle (small green square in the cell corner) or dragging it down.
Select all the formula cells in the helper column, press Ctrl+C to copy.
Click the first cell of the original column, press Ctrl+Alt+V to open Paste Special, select Values, click OK.
Delete the helper column — you now have the original column with all spaces removed.

This workflow — formula in a helper column, paste as values, delete helper — is the standard approach for any Excel text cleaning operation, including removing spaces in Excel with TRIM.

Clear Spaces in Excel — When TRIM Is Not Enough

TRIM handles regular spaces (ASCII character 32). But data from websites, PDFs, and some database exports contains non-breaking spaces (Unicode U+00A0, CHAR(160) in Excel). Non-breaking spaces look identical to regular spaces on screen but TRIM does not remove them — leaving your data still dirty after a TRIM operation.

If you have applied TRIM but your data still shows mismatched lookups or LEN() still returns an unexpectedly high count, non-breaking spaces are almost certainly the cause.

How to Clear Spaces in Excel Including Non-Breaking Spaces

Use this formula to clear spaces in Excel completely, including non-breaking spaces:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

This formula first converts all non-breaking spaces to regular spaces using SUBSTITUTE, then TRIM removes all extra regular spaces. The result is a cell that is truly clean of all space variants.

Strip Spaces in Excel Using Find and Replace

For a fast, formula-free approach to strip spaces in Excel, use the Find and Replace function. This method modifies cells in place without needing a helper column.

Remove Spaces with Find and Replace

Select the cells or columns you want to clean (or press Ctrl+A to select all).
Press Ctrl+H to open Find and Replace.
In the Find what field, press the spacebar once to enter a space character.
Leave the Replace with field completely empty.
Click Replace All.

Excel removes every space from every selected cell. This method removes all spaces — including spaces between words — so use it for data like product codes, IDs, phone numbers, and other values where spaces should not exist. For text where you want to preserve one space between words, use TRIM instead.

Remove Non-Breaking Spaces with Find and Replace

To remove non-breaking spaces via Find and Replace on Windows: open Find and Replace (Ctrl+H), click in the "Find what" field, hold Alt and type 0160 on the numeric keypad (Num Lock must be on), leave "Replace with" empty, click Replace All. On Mac, place your cursor in the "Find what" field and press Option+Space to insert a non-breaking space, then replace with nothing.

Excel Remove All Spaces — SUBSTITUTE for Complete Removal

TRIM removes extra spaces while keeping single spaces between words. When you need to remove all spaces in Excel with no spaces remaining — IDs, codes, concatenated strings — use SUBSTITUTE:

=SUBSTITUTE(A1," ","")

The SUBSTITUTE function replaces every occurrence of a space (the second argument) with nothing (empty string, the third argument). "New York City" becomes "NewYorkCity". "555 123 4567" becomes "5551234567". Combine with the CHAR(160) version for complete space removal:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ","")

This removes both regular spaces and non-breaking spaces, leaving values with zero spaces of any kind.

Excel Remove All Formatting — Clearing Cell Formatting

Excel remove all formatting refers to clearing the visual formatting of cells — font, color, borders, number format, alignment, and cell styles — while keeping the cell values and formulas intact. This is separate from removing spaces from text content.

How to Clear All Formatting in Excel

Select the cells or range you want to clear formatting from.
Go to the Home tab on the ribbon.
In the Editing group (far right), click the Clear dropdown (eraser icon).
Select Clear Formats.

All formatting is removed — the cells reset to default formatting (Calibri 11pt, no fill, no borders, General number format). The values are completely unchanged. You can also select all cells (Ctrl+A) before clearing to remove all formatting from the entire spreadsheet at once.

Clear All Contents and Formatting

To clear both content and formatting: select cells > Home > Clear > Clear All. This deletes values, formulas, formatting, and comments. Use with caution. For most excel remove all formatting tasks, you want Clear Formats (not Clear All) to preserve your data.

Remove Formatting When Pasting

When you paste data into Excel and want to paste values only (without source formatting), use Ctrl+Alt+V (Paste Special) and select Values. This pastes the text or numbers without any formatting from the source, giving you a clean paste that respects your spreadsheet's existing formatting.

Removing Spaces in Excel from Numeric Values

When numbers have spaces, Excel treats them as text. SUM, AVERAGE, and other numeric functions return 0 or errors. VLOOKUP against a number column fails. Even the cell alignment changes — numbers align right by default in Excel, but numbers-as-text align left, making it easy to spot the problem visually.

Convert Space-Contaminated Text Numbers to Real Numbers

Use VALUE combined with TRIM: =VALUE(TRIM(A1)). TRIM removes the spaces, VALUE converts the resulting text to a number Excel can calculate with.

Alternatively, after removing spaces with Find and Replace, select the affected cells. If a green triangle appears in the corner of cells with numbers stored as text, click the warning icon and select Convert to Number. This converts the entire selection at once.

Remove Spaces in Excel for Multiple Columns with Power Query

For large datasets with spaces across many columns, Power Query (Get & Transform Data) provides the most efficient approach. Power Query applies transformations to all rows in a column automatically, without formulas, and can be refreshed on demand as new data comes in.

How to Remove Spaces in Excel Using Power Query

Select any cell in your data range.
Go to Data tab > Get & Transform Data > From Table/Range (Excel converts your range to a Table if it is not already).
In the Power Query Editor, select the column(s) you want to clean (hold Ctrl to select multiple).
Go to Transform tab > Format > Trim. This removes leading and trailing spaces.
To also remove non-breaking spaces, go to Add Column > Custom Column and enter: Text.Trim(Text.Replace([YourColumn], Character.FromNumber(160), " "))
Click Close & Load to return the cleaned data to Excel.

When your source data updates, click Data > Refresh All and Power Query re-applies the space removal automatically — no need to re-run formulas.

Remove Spaces in Excel for Data Validation and Lookups

The most painful consequence of unremoved spaces in Excel is failed lookups. VLOOKUP, XLOOKUP, INDEX/MATCH, and COUNTIF all perform exact string comparisons. A lookup value with a trailing space will not match the same value without a trailing space — even though they look identical on screen.

Before building any lookup formula in Excel, run TRIM on both the lookup values and the lookup table data. Wrap your lookup in TRIM directly if you cannot clean the source data: =VLOOKUP(TRIM(A2),TRIM(B:B),1,FALSE). For XLOOKUP, the same principle applies: =XLOOKUP(TRIM(A2),TRIM(B:B),C:C).

For COUNTIF with space-contaminated criteria, use: =COUNTIF(B:B,TRIM(A2)). These wrapped-TRIM approaches are a temporary fix — cleaning the source data with TRIM or SUBSTITUTE is always the correct long-term solution.

Removing Spaces in Excel from AI-Generated or Pasted Content

Content generated by AI tools (ChatGPT, Claude, Gemini) and pasted into Excel often carries invisible Unicode characters beyond non-breaking spaces — zero-width spaces (U+200B), word joiners (U+2060), and byte-order marks (U+FEFF). These characters can disrupt text functions and data matching in Excel in the same way regular and non-breaking spaces do.

For AI-pasted content in Excel, run the text through the Format Remover on this site before pasting it into Excel. The Format Remover strips all invisible Unicode characters, markdown formatting, and typographic special characters in one click, producing clean plain text that behaves predictably when pasted into Excel cells. This is faster and more thorough than applying multiple nested Excel formulas to address each invisible character type individually.

Diagnosing Space Problems in Excel

Before running space-removal formulas, use these diagnostic techniques to understand the space problem in your data.

Check for Any Extra Spaces

=A1=TRIM(A1) — Returns FALSE if A1 has leading, trailing, or extra internal spaces. Filter your data for FALSE values to see which cells need cleaning.

Count the Extra Characters

=LEN(A1)-LEN(TRIM(A1)) — Shows how many extra space characters are in the cell. A result of 0 means no extra spaces. A result of 3 means 3 extra space characters to remove.

Check for Non-Breaking Spaces Specifically

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),"")) — Returns the count of non-breaking spaces. If TRIM alone is not solving your space problem and this formula returns a non-zero value, CHAR(160) SUBSTITUTE is needed.

Check for Other Invisible Characters

=LEN(A1)-LEN(CLEAN(A1)) — Returns the count of non-printable characters in the cell. If this is non-zero, use =TRIM(CLEAN(A1)) to remove them.

Summary: Which Excel Space-Removal Method to Use

Choosing the right method to remove spaces in Excel depends on what kind of spaces you have and what result you need.

=TRIM(A1) — Best for: removing leading/trailing spaces and reducing internal multiple spaces to one. Works on regular spaces only.
=TRIM(SUBSTITUTE(A1,CHAR(160)," ")) — Best for: data from websites or Word with non-breaking spaces mixed in.
=SUBSTITUTE(A1," ","") — Best for: removing all spaces including between words (codes, IDs, phone numbers).
=TRIM(CLEAN(A1)) — Best for: data from legacy systems with non-printable characters plus extra spaces.
Find and Replace (Ctrl+H) — Best for: fast removal of all spaces across large selections without formulas.
Power Query > Trim — Best for: recurring imports or large datasets across many columns.
Format Remover (this site) — Best for: AI-generated or richly formatted text with multiple invisible character types before pasting into Excel.

Remove Spaces in Excel — FAQ

Answers to the most common questions about removing, stripping, and clearing spaces from Excel cells and spreadsheet data.

How do I remove spaces in Excel?

The most common way to remove spaces in Excel is to use the TRIM function. In an empty cell next to your data, type =TRIM(A1) (replacing A1 with the cell containing your text). Press Enter. TRIM removes all leading spaces, trailing spaces, and reduces multiple spaces between words to a single space. To apply to all rows, drag the formula down the column. Then copy the formula results, paste as values (Paste Special > Values), and delete the original column. This gives you clean data with all extra spaces removed.

What is the TRIM function in Excel and how does it remove spaces?

The TRIM function in Excel removes all leading spaces (spaces before the first character), trailing spaces (spaces after the last character), and multiple consecutive spaces between words, reducing them to a single space. Syntax: =TRIM(text) where text is the cell reference or string you want to clean. TRIM does not remove single spaces between words — it preserves one space between each word. It also does not remove non-breaking spaces (Unicode U+00A0), which require a different approach using SUBSTITUTE or CLEAN combined with TRIM.

How do I remove all spaces in Excel, including spaces between words?

To remove every space in an Excel cell — including spaces between words — use the SUBSTITUTE function: =SUBSTITUTE(A1," ",""). This replaces every occurrence of a space character with nothing, removing all spaces completely. This differs from TRIM, which only removes extra spaces. Use SUBSTITUTE when you need values with no spaces at all — product codes, IDs, or any data where spaces are not valid. Combine with TRIM if you also want to first normalize the spacing before deciding how to use the data.

How do I use Find and Replace to remove spaces in Excel?

To remove spaces in Excel using Find and Replace: press Ctrl+H to open the Find and Replace dialog. In the "Find what" field, type a space (press the spacebar once). Leave the "Replace with" field empty. Click Replace All. Excel removes every single space from all selected cells. To target specific cells or columns, select them first before opening Find and Replace. Note: this method removes all spaces — including single spaces between words — not just leading and trailing spaces. Use TRIM first if you only want to remove extra spaces.

What is the difference between TRIM and CLEAN in Excel?

TRIM and CLEAN solve different types of text contamination in Excel. TRIM removes extra whitespace — leading spaces, trailing spaces, and multiple consecutive spaces. It targets the space character (ASCII 32). CLEAN removes non-printable characters — ASCII control characters (codes 0–31) that often appear in imported data, including line breaks, tabs, and other invisible characters. For thoroughly cleaning imported data, combine both: =TRIM(CLEAN(A1)). CLEAN strips the non-printable characters first, then TRIM removes the extra spaces that CLEAN may leave behind.

How do I remove leading spaces in Excel?

To remove leading spaces (spaces before the first visible character) in Excel, use =TRIM(A1). TRIM removes all leading spaces along with trailing spaces and extra internal spaces. If you only want to remove leading spaces and leave trailing spaces intact, use =MID(A1,FIND(MID(TRIM(A1),1,1),A1),LEN(A1)) — though in most cases TRIM is simpler and sufficient. Leading spaces in Excel are common when data is imported from text files, pasted from websites, or exported from databases that pad fields with spaces.

How do I remove trailing spaces in Excel?

To remove trailing spaces (spaces after the last visible character) in Excel, use =TRIM(A1). TRIM removes trailing spaces along with leading spaces and extra internal spaces. Trailing spaces in Excel are invisible and commonly cause problems in lookups — a VLOOKUP or MATCH will fail to find a value that has trailing spaces because "Smith " does not exactly equal "Smith". Running TRIM on your lookup values resolves these failures. For data imported from databases or CSV files, trailing spaces are extremely common.

Why does TRIM not remove all spaces in my Excel data?

TRIM does not remove non-breaking spaces (Unicode character U+00A0). Non-breaking spaces are common in data copied from websites, PDFs, and some database exports. They look identical to regular spaces but are a different character that TRIM does not recognize. To remove non-breaking spaces in Excel, use: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). This first converts non-breaking spaces to regular spaces using SUBSTITUTE and CHAR(160), then TRIM removes the extra spaces. If data is still not clean after TRIM, this non-breaking space issue is almost always the reason.

How do I strip spaces in Excel from numbers?

Spaces in numeric-looking values prevent Excel from treating them as numbers — sums, averages, and other formulas return errors or zero because Excel sees text, not numbers. To strip spaces from numbers in Excel: use =TRIM(A1) to remove extra spaces, then multiply the result by 1 or use VALUE() to convert the clean text to a true number: =VALUE(TRIM(A1)). Alternatively, use Find and Replace to remove all spaces, then select the column and use Data > Text to Columns to force numeric recognition. Numbers with embedded non-breaking spaces require the SUBSTITUTE+CHAR(160) method.

How do I remove non-breaking spaces in Excel?

Non-breaking spaces (CHAR(160), Unicode U+00A0) are invisible characters that look like regular spaces but are not removed by TRIM. They frequently appear in data copied from web pages, PDFs, and Word documents. To remove non-breaking spaces in Excel, use: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). The SUBSTITUTE function replaces every CHAR(160) with a regular space, then TRIM removes any extra regular spaces that result. For bulk removal across many cells, use Find and Replace: in the "Find what" field, hold Alt and type 0160 on the numeric keypad to enter the non-breaking space character, leave "Replace with" empty, and click Replace All.

How do I remove spaces in Excel from multiple columns at once?

To remove spaces in Excel from multiple columns at once: select the entire range across all columns you want to clean, use Find and Replace (Ctrl+H) with a space in "Find what" and nothing in "Replace with", and click Replace All. This removes all spaces from all selected cells in one operation. For a TRIM-based approach across multiple columns: insert a helper column, enter =TRIM(A1), copy the formula across all columns that need cleaning, copy all the formula results, paste as values over the original data, and delete the helper columns. For large datasets, Power Query (Get & Transform) handles multi-column space removal more efficiently.

What does "excel remove all formatting" mean?

"Excel remove all formatting" refers to clearing cell formatting attributes — number format, font, fill color, borders, alignment, and cell styles — while keeping the cell values intact. This is different from removing spaces from text. To clear all formatting in Excel: select the cells, go to Home tab > Editing group > Clear dropdown > Clear Formats. This resets every formatting attribute to default while leaving the content (text, numbers, formulas) unchanged. If you want to remove both formatting and extra spaces, run Clear Formats first, then apply TRIM to the content.

How do I clear all formatting in Excel without deleting data?

To clear all formatting in Excel without deleting data: select the cells or range you want to clean, go to the Home tab, find the Editing group on the right side of the ribbon, click the Clear dropdown arrow (eraser icon), and select Clear Formats. Excel removes all formatting attributes — colors, fonts, borders, number formats — and resets the cells to default formatting while keeping every value, formula, and text entry unchanged. You can also use Ctrl+Shift+Z (or the Format Cells dialog) to selectively reset specific formatting attributes if you only want to remove some formatting.

How do I remove spaces in Excel from imported CSV or text file data?

Data imported from CSV and text files frequently contains leading and trailing spaces in every field, especially from legacy systems and database exports that pad fields to fixed widths. To remove spaces in Excel from imported data: after importing, select the column with spaces, insert a helper column, enter =TRIM(A1) (or =TRIM(CLEAN(A1)) for data with non-printable characters), copy the formula down the entire column, select the formula results, copy them, paste as values only (Ctrl+Alt+V > Values), then delete the original messy column. For recurring imports, use Power Query to apply TRIM transformation automatically on refresh.

How do I remove spaces in Excel using Power Query?

Power Query (Get & Transform Data) in Excel provides a Transform > Format > Trim option that removes leading and trailing spaces from selected columns without formulas. To use it: select your data range, go to Data > Get & Transform Data > From Table/Range, select the column you want to clean in the Power Query Editor, go to Transform tab > Format > Trim. To also remove extra internal spaces, add a custom column with Text.Trim([ColumnName]). Click Close & Load to return the cleaned data to Excel. Power Query Trim is especially useful for recurring data imports where you want the cleaning to happen automatically every time you refresh.

How do I strip spaces in Excel when SUBSTITUTE removes too much?

If SUBSTITUTE removes spaces you want to keep (like single spaces between words), use a more targeted approach. For removing only leading and trailing spaces while keeping internal spacing: use =TRIM(A1). For removing only multiple consecutive spaces (reducing them to single spaces): =TRIM(A1) handles this too. For removing spaces only at the start: combine MID and FIND. For removing spaces only at the end: use RTRIM alternative formulas. In most cases, TRIM is the right function — it removes leading spaces, trailing spaces, and extra internal spaces while preserving single spaces between words. Only use SUBSTITUTE if you want to remove all spaces including between words.

Can I remove spaces in Excel on a Mac?

Yes. All Excel space-removal methods work identically on Mac. The TRIM, SUBSTITUTE, CLEAN, and VALUE functions work the same way. Find and Replace is accessed the same way (Cmd+H on Mac instead of Ctrl+H on Windows). Power Query is available in Excel for Mac (version 16.x and later). The CHAR(160) non-breaking space method also works on Mac. The only difference is keyboard shortcuts for some operations — on Mac, use Cmd instead of Ctrl for most shortcuts. The non-breaking space Find and Replace requires accessing the special character differently: on Mac, you can paste a non-breaking space (Opt+Space) directly into the Find field.

How do I remove double spaces in Excel?

To remove double spaces (and any multiple consecutive spaces) in Excel, use =TRIM(A1). TRIM collapses all runs of multiple spaces between words to a single space. A cell containing "John Smith" (two spaces between first and last name) becomes "John Smith" (one space) after TRIM. For removing all double spaces across a column: insert a helper column with =TRIM(A1), drag down, copy the results, paste as values, and delete the original column. If TRIM does not fully solve it, check for non-breaking spaces (CHAR(160)) mixed in with regular spaces.

Why is my Excel VLOOKUP failing because of spaces?

Excel VLOOKUP fails when the lookup value contains spaces (leading, trailing, or extra internal spaces) that do not exist in the lookup range, or vice versa. "Smith" and "Smith " are different strings to Excel — the trailing space makes them not match. To fix VLOOKUP failures caused by spaces: wrap both the lookup value and the values in your lookup range with TRIM. Example: =VLOOKUP(TRIM(A2), B:C, 2, FALSE). If the lookup range has non-breaking spaces, use TRIM(SUBSTITUTE(B2,CHAR(160)," ")) instead. Running TRIM on your data before building the VLOOKUP is the cleaner long-term fix.

How do I use CLEAN and TRIM together in Excel?

Combining CLEAN and TRIM in Excel gives you the most thorough text cleaning for imported data. CLEAN removes non-printable characters (ASCII 0–31, including line breaks and tab characters). TRIM then removes leading/trailing spaces and extra internal spaces. The combined formula is: =TRIM(CLEAN(A1)). Always run CLEAN first (inside the formula), then TRIM on the outside. This order matters because CLEAN can leave extra spaces where it removed non-printable characters, and TRIM cleans those up. For data imported from legacy systems, external APIs, or copy-pasted from PDFs, =TRIM(CLEAN(A1)) is the most reliable first pass.

How do I remove spaces before numbers in Excel to make them sum correctly?

When numbers have leading spaces, Excel treats them as text and SUM returns zero for those cells. To fix: use =VALUE(TRIM(A1)) which removes the leading spaces and converts the result to a true numeric value. You can also select the cells with space-contaminated numbers, go to Data > Text to Columns > Finish (without changing settings) — this sometimes forces Excel to recognize numeric values. Another method: in an empty cell, type 1, copy it, select your space-contaminated number cells, Paste Special > Multiply — this multiplies each value by 1, forcing numeric conversion. Verify success by checking that SUM now returns the correct total.

How do I remove spaces in Excel between digits in phone numbers?

Phone numbers often contain spaces for readability (e.g., "555 123 4567") that need to be removed for data systems that expect no spaces. Use =SUBSTITUTE(A1," ","") to remove all spaces from phone numbers — this differs from TRIM because it removes every space, not just extra ones. Copy the result column, paste as values, and delete the original. If phone numbers came from a web copy or have non-breaking spaces, also add SUBSTITUTE for CHAR(160): =SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),"")," ",""). Always format the resulting column as Text (not Number) to preserve leading zeros in phone numbers.

What is the fastest way to remove spaces in Excel for a whole spreadsheet?

The fastest way to remove spaces in Excel across an entire spreadsheet: (1) Press Ctrl+A to select all cells. (2) Press Ctrl+H to open Find and Replace. (3) Type a single space in "Find what". (4) Leave "Replace with" empty. (5) Click Replace All. This removes all spaces from every cell in the sheet in one operation. Caution: this removes all spaces including spaces between words. If you want to preserve word spacing and only remove leading/trailing/extra spaces, you need to use the TRIM formula approach column by column. For most data cleaning purposes where you want no spaces at all, the Ctrl+A Find-Replace method is fastest.

How do I remove spaces in Excel that came from copying and pasting from a website?

Data copied from websites often contains non-breaking spaces (CHAR(160)) that are invisible but prevent correct matching and summing in Excel. Regular TRIM does not remove them. The reliable formula for website-pasted data is: =TRIM(SUBSTITUTE(A1,CHAR(160)," ")). This converts all non-breaking spaces to regular spaces, then TRIM removes the extra regular spaces. For bulk cleaning: apply the formula to a helper column, copy the results, paste as values over the original data. If data also has line breaks from the website, add CLEAN: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))).

How do I check if a cell in Excel has hidden spaces?

To check if an Excel cell has hidden spaces: (1) Use LEN — compare LEN(A1) to LEN(TRIM(A1)). If LEN(TRIM(A1)) is smaller, the cell has extra spaces. (2) Use =A1=TRIM(A1) — returns FALSE if A1 has leading/trailing/extra spaces. (3) Select the cell and look at the formula bar — leading spaces are visible as extra space before the first character. (4) For non-breaking spaces, compare LEN(A1) to LEN(SUBSTITUTE(A1,CHAR(160),"")) — if different, non-breaking spaces are present. These diagnostic formulas help you locate which cells need space removal before running the cleaning operation.

What causes spaces to appear in Excel cells when importing data?

Spaces appear in Excel cells during data import for several reasons. Database exports often pad fields to fixed column widths with trailing spaces. CSV files from legacy systems include leading spaces after commas. Copy-pasting from websites brings non-breaking spaces from HTML markup. PDFs add spaces around extracted characters depending on how the PDF was created. API responses in JSON or XML format sometimes include leading/trailing spaces in string values. Mail merge data from Word documents can carry AutoCorrect-inserted non-breaking spaces. Each source has a typical space pattern — the TRIM and SUBSTITUTE+CHAR(160) methods handle all of these cases.