Have you ever copied text into Excel and noticed unwanted spaces at the beginning of each cell? While you could delete them one by one, that’s really time-consuming, especially if you’re working with dozens or hundreds of rows.

In this tutorial, you’ll learn how to remove spaces at the beginning of text in Microsoft Excel using a formula that combines the FIND, MID, TRIM, and LEN functions. You’ll also learn how to fix a common issue where the formula doesn’t work because your data contains non-breaking spaces copied from a website or PDF.

What You’ll Learn

  • How to remove spaces at the beginning of text
  • Why the TRIM function alone doesn’t always solve the problem
  • How FIND, MID, TRIM, and LEN work together
  • How to copy the formula down an entire column
  • Why you should paste values after using the formula
  • How to fix non-breaking spaces copied from websites

Common Uses

  • Cleaning imported data
  • Removing leading spaces after copying from websites
  • Preparing data for lookups or formulas
  • Cleaning mailing lists or contact lists
  • Standardizing imported reports
  • Improving data consistency before sorting or filtering

Requirements / Compatibility

This tutorial was originally recorded in 2020, and the formulas continue to work in current versions of Microsoft Excel, including:

  • Microsoft 365
  • Excel 2024
  • Excel 2021
  • Excel 2019
  • Excel 2016
  • Excel for Mac

The troubleshooting section for non-breaking spaces is also still applicable when working with data copied from websites, PDFs, or other external sources.

Practice & Resources

No practice workbook is required for this tutorial.

Simply open any worksheet containing text with unwanted spaces at the beginning of each cell and follow along with the steps below.

Video Tutorial

Breaking Down the Formula

The formula used in this tutorial combines several Excel functions, each with a specific job. Together, they locate the first real character in a cell and return everything from that point forward, effectively removing unwanted spaces at the beginning of the text.

TRIM – Remove Extra Spaces

=TRIM(A1)

The TRIM function removes extra spaces from text while keeping a single space between words.

For example: New York becomes New York

However, TRIM isn’t always enough when you only want to remove leading spaces or when your data contains non-breaking spaces copied from another source.

Why it’s used:

  • Cleans up unnecessary spaces.
  • Standardizes spacing between words.

FIND – Locate the First Real Character

The FIND function searches the text and determines where the first non-space character appears.

Instead of simply deleting spaces, the formula first asks:

“Where does the actual text begin?”

Why it’s used:

  • Identifies the starting position of the real text.
  • Allows the remaining functions to ignore the leading spaces.

MID – Return the Remaining Text

=MID(A1,start_position,LEN(A1))

Once FIND determines where the text begins, the MID function returns everything from that point to the end of the cell.

Rather than deleting spaces individually, MID simply starts reading at the first meaningful character.

Why it’s used:

  • Returns the cleaned text.
  • Eliminates all leading spaces in one step.

LEN – Determine How Much Text to Return

=LEN(A1)

The LEN function counts the number of characters in the original cell.

That number tells MID how many characters to return so the entire text is preserved.

Why it’s used:

  • Ensures none of the actual text is cut off.
  • Allows the formula to work with text of any length.

Why Do These Functions Work Together?

Each function solves a small part of the problem:

  • TRIM cleans up unnecessary spaces.
  • FIND identifies where the text actually begins.
  • MID returns the text starting at that position.
  • LEN ensures the entire text is returned.

Together, they remove leading spaces while preserving the rest of the cell’s contents.

What If the Formula Doesn’t Work?

If your text came from a website, PDF, or another application, the spaces may not actually be standard spaces.

Many websites use non-breaking spaces, which look identical to regular spaces but use a different character behind the scenes. Because of that, Excel’s TRIM function and similar formulas may not recognize them.

The fix is simple:

  1. Copy one of the non-breaking spaces.
  2. Press Ctrl + H to open Find and Replace.
  3. Paste the copied character into Find what.
  4. Type a normal space into Replace with.
  5. Click Replace All.
  6. Run the formula again if needed.

Once the non-breaking spaces have been converted to regular spaces, the formula works as expected.

Step-by-Step Instructions

Remove Leading Spaces

  1. Insert a blank column beside your data.
  2. Copy the formula into the first row of the new column.
  3. Replace each placeholder cell reference with the cell containing your text (for example, C2).
  4. Press Enter.
  5. Drag the formula down to the remaining rows.
  6. Copy the results.
  7. Use Paste Special → Values to replace the formulas with the cleaned text.

If You Still See Spaces

  1. Copy one of the remaining spaces.
  2. Press Ctrl + H.
  3. Replace the copied non-breaking space with a normal space.
  4. Run the formula again if necessary.

Additional Tips

  • Paste Values before deleting the original data so you keep the cleaned text instead of the formulas.
  • If your data came from a website or PDF, non-breaking spaces are often the reason formulas don’t work.
  • Test the formula on one row first before applying it to a large dataset.
  • Keep the original data until you’ve confirmed the results are correct.

Related Features

  • How to Remove Extra Spaces in Excel
  • How to Sort & Filter in Excel
  • Flash Fill
  • TEXTSPLIT
  • LEFT
  • RIGHT
  • MID

Related

How to Remove Extra Spaces in Excel

Learn how to remove extra spaces from text in Microsoft Excel using the TRIM function. This beginner-friendly tutorial explains what TRIM removes, when to use it, and why it’s one of the most useful functions for cleaning imported or inconsistent data.

How to Hide Zero Values (0s) in Excel

Learn two easy ways to hide zero values in Microsoft Excel without changing your formulas. This step-by-step tutorial demonstrates both the built-in worksheet option and a Conditional Formatting method to create cleaner spreadsheets, reports, and templates. Includes updated instructions and screenshots for current versions of Excel.

How to Create an Order Form with Drop-Down Menus in Microsoft Excel

Learn how to create a professional order form in Microsoft Excel using drop-down menus, Data Validation, and named ranges. This step-by-step tutorial also covers formatting dates, ZIP codes, and credit card fields to build cleaner, more accurate spreadsheets for real-world business use.

Stay Updated

There are two easy ways to get notified when new tutorials are published:

Subscribe on YouTube for new video tutorials: @SimpleSoftwareTutorials

or enter your email below to receive new blog posts directly in your inbox: