Excel Remove Space Formula Generator
Fix TRIM() not working, clean Shopify CSVs, and remove invisible "Ghost Spaces" (Char 160) that break your VLOOKUPs.
Formula Generator
Customize your cleaning formula below.
Standard spaces (Char 32). Common for manually typed data.
Includes Non-Breaking Spaces (Char 160). Select this if TRIM isn't working.
Instructions: Paste this into a helper column (e.g., B2), drag down, then Copy & Paste as Values.
How to Use This Generator
Understanding where your "dirty data" comes from is half the battle. If you pasted data from a website, Jira, or a PDF, you likely have Non-Breaking Spaces (Char 160). Excel's standard TRIM function ignores these, which is why your VLOOKUPs return #N/A.
Smart Trim Mode
Best for names, addresses, and sentences. It removes "ghost" whitespace from the start and end, and ensures only single spaces remain between words.
Remove All Mode
Best for Part Numbers, SKUs, IBANs, or Phone Numbers where NO spaces should exist at all.
Step-by-Step Instructions
- Diagnose: If
=TRIM(A1)didn't work, select "Web / HTML" in the generator above. - Generate: Copy the formula provided.
- Apply: Paste it into the top cell of a new column next to your data.
- Fill: Double-click the fill handle to apply it to all rows.
- Lock It In: Copy the new column and Paste as Values to remove the formula.
Common Use Cases
Shopify Product Import Failed
"Validation failed: Variant Fulfillment Service can't be blank"
The Problem: Your CSV field contains "manual " (6 chars) instead of "manual" (5 chars). That invisible tail makes Shopify reject the entire row.
The Fix: Use our excel formula to clean data for shopify import. Select Target: Web/HTML + Mode: Standard Trim to strip the hidden tail without breaking the text.
Salesforce Data Loader Error
"Bad value for restricted picklist field: Industry"
The Problem: "Healthcare " (Char 160) does not match "Healthcare" in your Picklist Value Set.
The Fix: Use remove non-breaking space excel formula logic. Our generator replaces Char 160 with Char 32, then trims it ensuring a perfect match.
SQL Server String Truncated
"String or binary data would be truncated"
The Problem: You are trying to insert 55 characters into a VARCHAR(50) column. The data looks short ("John Doe"), but has 20 invisible spaces at the end padding the length.
The Fix: Before running your INSERT query, use Standard Smart Trim to cut the fat.
VLOOKUP #N/A Errors
The Problem: "ID-123" does not equal "ID-123 ". Excel sees them as different values, causing excel trim function not removing spaces confusion.
The Fix: Clean BOTH the lookup value column and the table array column using the generated formula.
Power BI Merge Queries 0 Matches
The Problem: 0 matches found when merging tables, even though keys look identical. Often caused by hidden Line Feeds (Char 10) from Alt+Enter.
The Fix: Use Target: Standard + Mode: Remove Line Breaks. This strips Char 10 and Char 13, enabling Power BI to match rows correctly.
Frequently Asked Questions
Why is TRIM not removing spaces?
TRIM only removes ASCII 32 (standard spaces). It cannot touch ASCII 160 (Non-breaking spaces) which are common in data copied from the web. Our generator creates an excel substitute function to remove non-breaking space.
How do I remove the space between numbers?
Select the 'Remove All Spaces' mode above. This uses the SUBSTITUTE function to replace every space instance with nothing, effectively performing a bulk remove trailing spaces in excel including internal ones.
Will this fix 'Number stored as text'?
Yes. Check the 'Convert result to number' box. It wraps your formula in a VALUE() function which forces Excel to treat the result as a number.
How to remove non-breaking space in Excel formula?
You need to use SUBSTITUTE(A1, CHAR(160), " "). Our tool generates this for you automatically so you don't have to remember the ASCII code.
Can I use this to clean data for Shopify import?
Absolutely. The 'excel formula to clean data for shopify import' is one of our most popular use cases. It ensures 'Variant Fulfillment Service' and other strict fields don't have trailing whitespace.
How to remove carriage return in Excel?
Carriage returns (Char 13) and Line Feeds (Char 10) are removed by the CLEAN() function. Our generator includes this in the Standard mode.
What is the difference between Char(160) and Char(32)?
Char(32) is a standard space. Char(160) is a non-breaking space used in HTML. Visually they look identical, but Excel sees them as different characters, which breaks VLOOKUPs.
Does this work for removing spaces from phone numbers?
Yes. Use the 'Remove All Spaces' mode. It will turn '+1 555 0199' into '+15550199'.
Why do I get 'String or binary data would be truncated' in SQL?
This happens when you try to insert a string longer than the defined column width (e.g. VARCHAR(50)). Often, the data fits visually but has invisible trailing spaces. Trimming fixes this.
Is there a way to do this without formulas?
You can use Find & Replace (Ctrl+H) to replace spaces, but be careful as it replaces ALL spaces. For safe, selective cleaning, formulas are better. Or use our automatic SheetClean tool (see sidebar).
Need Automatic Cleaning?
Don't want to mess with formulas column by column?
- Upload whole workbook
- Cleans standard & ghost spaces
- Preserves all formatting
- Detects "Number as Text"