Bulk Remove Text and Spaces in Excel

Need to remove text and extra spaces in Excel? You can clean cells for free using native Excel functions and tools, or remove unwanted characters in one click with the XLclick add-in. Choose the method that fits your data and simplify your Excel data-cleaning workflow.

Download Excel Add-in

Excel vs. XLclick: Compare Your Text-Cleaning Options

Excel offers several ways to remove unwanted spaces and characters, but many require formulas, helper columns, or repeated manual steps. XLclick combines multiple text-removal options in one interface, making bulk cleanup faster and easier.

FeatureNative Excel MethodsXLclick Remove Text and Spaces
Remove extra spacesTRIM formulaBuilt-in cleaning option
Remove hidden charactersClean and SUBSTITUTE formulasDedicated cleaning option
Remove first or last charactersLEFT, RIGHT, LEN, or MID formulasDirect removal options
Remove text before or after a stringFormulas or manual replacementsBuilt-in options
Regex-based removalRequires compatible functions or other toolsCustom regex patterns
Remove all numbersComplex formulas or replacementsBuilt-in option
Bulk processingOften requires extra setupOne streamlined workflow
Preview resultsCheck formula output manuallyLive preview

Important: XLclick applies changes directly to the selected cells, so the original values can be overwritten. Native Excel formulas can preserve the source data in a separate column, but pasting results over the original cells can also cause data loss. Make a backup before bulk operations. If you need results on a new worksheet, create or copy one manually before cleaning.

Free Ways to Remove Unwanted Text and Spaces in Excel

You can clean Excel data without installing an add-in. Here are several useful native methods, along with their limitations, so you can choose the right approach for your spreadsheet.

1. Remove Extra Spaces with TRIM

The TRIM function removes leading and trailing spaces and reduces repeated internal spaces to single spaces. If cell A2 contains text with unnecessary spaces, enter:

=TRIM(A2)

This is particularly useful for cleaning names, product descriptions, and imported lists before sorting or matching data.

Limitations: TRIM does not remove every type of invisible character. Nonbreaking spaces copied from websites may remain, requiring additional functions such as SUBSTITUTE. You must also fill the formula down and replace the original data manually if needed.

2. Clean Hidden Characters with CLEAN

The CLEAN function removes many nonprinting characters from text. For example:

=CLEAN(A2)

You can combine it with TRIM to clean text more thoroughly:

=TRIM(CLEAN(A2))

Limitations: CLEAN targets specific nonprinting characters, not every invisible Unicode character or HTML entity. Complex imported data may still need further processing.

3. Remove the First or Last Characters

Use RIGHT, LEFT, and LEN to remove a fixed number of characters. To remove the first three characters from A2, use:

=RIGHT(A2,LEN(A2)-3)

To remove the last three characters, use:

=LEFT(A2,LEN(A2)-3)

Limitations: These formulas require the correct character count. If the unwanted prefix or suffix varies between cells, a fixed number may remove too much or too little text. You also need to handle short or empty cells carefully.

4. Remove Text Before or After a Specific String

Excel provides text functions such as FIND, SEARCH, LEFT, MID, and RIGHT to extract parts of a string. In newer Excel versions, TEXTBEFORE and TEXTAFTER can make this easier. For example, =TEXTAFTER(A2,"-") returns the text after the first hyphen.

Limitations: Function availability depends on your Excel version. Formulas may also need extra logic when the delimiter is missing, appears multiple times, or uses inconsistent formatting.

5. Use Find and Replace to Delete Specific Text

Press Ctrl+H to open Find and Replace. Enter the unwanted text in the Find what field, leave Replace with empty, and choose Replace All to remove matching occurrences.

Limitations: This method is useful for known strings but less practical for complicated patterns or varied prefixes and suffixes. Replacing all matches can also remove text you intended to keep, so check the selection and results carefully.

Remove Text and Spaces in One Click with XLclick

Every cell in your Excel list has the same unwanted prefix, suffix, or hidden spaces? Remove Text and Spaces strips them all in bulk — no formulas, no manual editing.

Essential Settings for Remove Text and Spaces

Remove Text and Spaces Settings

To open Remove Text and Spaces, go to the XLclick tab, find the Fix and Clean group, then click Refine > Remove Text and Spaces. The tool overwrites your selection directly.

The panel walks you through two steps:

1. SELECT DATA — Click Select and highlight the cells you want to clean.

2. REMOVE METHOD — Choose what to strip from each cell:

  • First N characters — removes the first N characters from each cell. Set the value in the N = field.
  • Last N characters — removes the last N characters from each cell.
  • Before the text — removes everything that appears before a specific string.
  • After the text — removes everything that appears after a specific string.
  • Regex — removes text matching a custom regular expression pattern.
  • All numbers — strips all numeric characters from every cell.
  • Clean spaces and HTML — removes invisible characters, HTML entities, and extra spaces that sneak in when copying from websites.

The LIVE PREVIEW shows results in real time. Click Remove Now to apply, or Cancel to exit.

Real-World Scenarios: Top Use Cases for Excel Remove Text and Spaces

A Data Entry Manager Removing ID Prefixes From an Entire Product Code Column

A data entry manager received a product export where every code had a prefix like ID: in front — ID: 10042, ID: 10043. The internal system only accepted the raw numbers, so every code needed the prefix removed before import. With 2,000 rows, doing it manually was out of the question.

Remove Text and Spaces with Before the text set to the colon-space removed the prefix from every cell at once. The cleaned codes were ready to import in under a minute.

A Freelance VA Cleaning Copy-Pasted Data Full of Invisible Characters

A freelance virtual assistant copied customer data from a web portal into Excel. The cells looked fine on screen, but filters and lookups weren't working — invisible characters and HTML entities were hiding inside the text, breaking everything.

Remove Text and Spaces with Clean spaces and HTML stripped all invisible characters from the selection in one click. Filters and lookups started working correctly immediately after.

A Small Business Owner Stripping Units From a Price List

A small business owner had a pricing spreadsheet where every value ended with EUR — like 29.99 EUR. Before importing into her invoicing software, the currency label had to be removed from every cell. With 500 products, manually editing each one would have taken hours.

Remove Text and Spaces with After the text set to the space before EUR removed the suffix from every price cell in one pass. The clean numbers were import-ready instantly.

A Marketing Analyst Removing Trailing Reference Codes From Campaign Names

A marketing analyst had a campaign name column where every entry ended with an internal tracking code — like Summer Sale 2024 [REF-447]. For the client report, the reference codes had to be stripped, leaving only the readable campaign names.

Remove Text and Spaces with Before the text set to the bracket character removed everything from the bracket onward. All 300 campaign names were cleaned in seconds.

An E-Commerce Team Removing Leading Zeros and Formatting From Order IDs

An e-commerce team had order IDs imported with leading zeros and spaces — like 00123 or 456 . Their fulfillment system rejected anything that wasn't a plain integer, and manual cleanup across thousands of orders wasn't feasible.

Remove Text and Spaces with Clean spaces and HTML removed extra spaces, and First N characters stripped the leading zeros. The order IDs were clean and system-ready in seconds.

Author Image
Federico Magni SEO Specialist since 2012

Excel has always been my laboratory. After years of navigating data-heavy workflows, I created XLclick: the definitive add-in that simplifies complex analysis into a single click. It’s built for pros who want to spend less time on spreadsheets and more time on strategy.