Pinterest Pixel

How to Remove Leading and Trailing Spaces in Excel

Bryan
In Excel, you may sometimes find unwanted and extra spaces in your data.
It might take a considerable amount of time to clean this data and make it perfect for further analysis.

So, it is extremely important to Excel remove trailing spaces! Leading spaces (at the start of the text), Trailing spaces (at the end of the text) and Non-Breaking spaces (prevents line breaks from occurring at a particular point) usually get in the way when we want to perform operations in Excel.

In Excel, you may sometimes find unwanted and extra spaces in your data. It might take a considerable amount of time to clean this data and make it perfect for further analysis. So, it is extremely important to Excel remove trailing spaces! Leading spaces (at the start of the text), Trailing spaces (at the end of the text) and Non-Breaking spaces (prevents line breaks from occurring at a particular point) usually get in the way when we want to perform operations in Excel.

Key Takeaways:

  • Utilize the built-in TRIM function in Excel to quickly eliminate both leading and trailing spaces from text strings within cells. This function is straightforward and efficient, ensuring that your data is cleaned without unnecessary manual effort, thus maintaining the integrity of your worksheets and the accuracy of any related formulas.
  • Employ the SUBSTITUTE and CHAR functions for situations where non-breaking spaces (CHAR(160)) persist after using TRIM, which only removes regular spaces. By replacing non-breaking spaces with empty strings using SUBSTITUTE, you can ensure that all types of extra spaces are cleared from your data.
  • Automation of space removal can be achieved through features like Find and Replace, which allows you to search for multiple consecutive spaces and replace them with a single space or remove them altogether. This Excel feature can greatly reduce the time spent on cleaning up data with extra spaces, leading to more efficient data management and analysis.

Let’s look at each one of these methods on how to remove leading spaces in Excel, trailing spaces as well as non-breaking spaces!

TRIM Function to Remove Extra Spaces

What does it do?

Removes unneeded spaces in your text, except single spaces in between words

Formula breakdown:

=TRIM(text)

What it means:

=TRIM(text that you want extra spaces to be removed)

In the example below, you will see the text contains extra spaces at the beginning, middle, and even end of the sentence.

Let’s remove the unwanted spaces and keep only a single space between words.

Follow the step-by-step tutorial below on Excel remove trailing spaces and leading spaces and download this workbook to follow along:

download excel workbookHow-to-Clean-Leading-and-Trailing-Spaces.xlsx

 

STEP 1:  Enter the TRIM formula in cell C7.
=TRIM(
How to Remove Leading and Trailing Spaces in Excel

STEP 2:  Enter the argument required for this formula – text from which you want extra spaces to be removed.
=TRIM(B7)
How to Remove Leading and Trailing Spaces in Excel

The spaces get removed with no issues:

How to Remove Leading and Trailing Spaces in Excel

STEP 3:  Now, copy-paste the formula below to the cell range C7:C10.

How to Remove Leading and Trailing Spaces in Excel

That’s it!

All the unwanted spaces are gone.

 

It is quite easy to Excel remove trailing spaces and leading spaces using the Trim function.

In the example below, we have used the TRIM function on both cell D7 and cell D8.

How to Remove Leading and Trailing Spaces in Excel  How to Remove Leading and Trailing Spaces in Excel

Since cell D8 has non-breaking spaces, the TRIM formula will not remove these spaces. The leading space is still there!

The trim function can only remove normal spaces (ASCII character code 32). Non-breaking space has an ASCII character code 160 and it cannot be removed using the trim function.

 

What are non-breaking spaces you ask?

Sometimes data downloaded as text uses non-breaking spaces for formatting purposes i.e. it prevents an automatic line break in between these spaces.

This is represented by CHAR(160):

How to Remove Leading and Trailing Spaces in Excel

This looks like a pain! How do we clean these?

Let’s move forward and understand how to remove non-breaking spaces using SUBSTITUTE below!

Substitute Function to Remove Non-Breaking Spaces

We will use the SUBSTITUTE Formula to remove the non-breaking spaces depicted by CHAR(160).

The goal is to replace the non-breaking spaces with normal spaces.

 

STEP 1: Enter the SUBSTITUTE formula in cell D9.
=SUBSTITUTE(
How to Remove Leading and Trailing Spaces in Excel

STEP 2: Enter the first argument – the cell that contains the text you wish to substitute.
=SUBSTITUTE(C9
How to Remove Leading and Trailing Spaces in Excel

STEP 3: Enter the second argument – the existing text you wish to substitute.
=SUBSTITUTE(C9,CHAR(160),
How to Remove Leading and Trailing Spaces in Excel

STEP 4: Enter the third argument – the new text you wish to replace with.
=SUBSTITUTE(C9,CHAR(160)," ")
How to Remove Leading and Trailing Spaces in Excel

Note: CHAR(160) represent non-breaking spaces in a text.

STEP 5: Now that we do not have the non-breaking spaces anymore, let us use the TRIM Formula!
=TRIM(SUBSTITUTE(C9, CHAR(160), " "))
How to Remove Leading and Trailing Spaces in Excel

Now all of your leading and trailing spaces are cleaned up!

How to Remove Leading and Trailing Spaces in Excel

 

Find & Replace to remove all spaces

You can use the find and replace feature in Excel to amend data within seconds. In the example below, you have a list of few numbers and you wish to remove any spaces between these numbers.

How to Remove Leading and Trailing Spaces in Excel

To remove all space characters from a cell, follow the steps below:

STEP 1: Select the cell range from which you want to remove spaces.

How to Remove Leading and Trailing Spaces in Excel

STEP 2: Go to  Home > Find & Select > Replace or Press Ctrl + H to open the Find & Replace dialog box.

How to Remove Leading and Trailing Spaces in Excel

STEP 3: Press the Space bar in the Find what field to insert a space and keep the Replace with field empty.

How to Remove Leading and Trailing Spaces in Excel

STEP 4: Click the Replace All button and then click OK.

How to Remove Leading and Trailing Spaces in Excel

All the spaces are now removed!

How to Remove Leading and Trailing Spaces in Excel

This is how you can Excel remove trailing spaces, trailing spaces, and non-breaking spaces, too!

Streamlining Your Data without Formulas

Formula-free methods to clean your data

Ever find yourself drowning in data that’s as messy as a toddler’s playroom? You’re not alone! Cleaning it up might sound like a daunting task, but I’ve got some nifty tricks that don’t even require formulas. Imagine that! Let’s talk about using Excel’s ‘Text to Columns’ feature. This handy tool can act like a data broom, sweeping away unwanted spaces without breaking a sweat. Just select your cells, hit that ‘Data’ tab, and choose ‘Text to Columns.’ Pick ‘Delimited,’ select ‘Space’ as your delimiter, and boom – clean cells!

How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline

Utilizing Excel’s built-in tools for data cleansing

Excel is stocked with an arsenal of tools ready to get your data in tip-top shape, and you just have to know where to look! Apart from the ‘Text to Columns’ wizard, you can engage the ‘Find & Replace’ function to search for double spaces and zap them to a single space. For those pesky non-printable characters, Excel’s ‘CLEAN’ function is like a digital shower for your cells. Simply input ‘=CLEAN(cell reference)’ and let Excel scrub away those invisible gremlins that mess up your data set.

Real-World Scenarios and Solutions

Cleaning up phone numbers and text entries

When your phone numbers look like they’ve been through a tornado, it’s time to straighten them out. Start by stripping away those unwanted characters—spaces, dashes, dots, you name it. The ‘SUBSTITUTE’ function comes in handy for this. Just specify what you want to kick to the curb, and Excel will align those digits in a neat row, ready for reformatting. Then, apply the ‘TEXT’ function to get the numbers in a common format that suits your style, whether it’s international, local, or extension-inclusive.

Prepping data for analysis and reporting

Now, let’s equip you for the number crunching battle. Data prepped for analysis or reporting should be like a well-oiled machine—no squeaky parts. Kickstart your Excel engines by using ‘Pivot Tables’ to transform your hodgepodge of data into organized masterpieces. With your spaces trimmed and your cells squeaky clean, grouping data, calculating averages, or spotting trends becomes a walk in the park. Next up, make sure all your text data is consistently capitalized with the ‘PROPER’, ‘UPPER’, or ‘LOWER’ functions so that your report doesn’t look like a jumbled case of mistaken identity.

Cleaning Data with Excel's PROPER Formula

FAQ – Trimming Spaces Like a Pro

Why does Excel have issues with non-breaking spaces?

Ah, the notorious non-breaking spaces – think of them as undercover agents in your data, they’re not the usual space character Excel recognizes. These cheeky characters come from other programs or web content and prevent automatic line breaks, but Excel’s ‘TRIM’ function doesn’t realize they’re there—it only recognizes the standard space (ASCII 160). To spot and zap these hidden infiltrators, you’ll need to call in the ‘SUBSTITUTE’ function as your secret agent.

How to Remove Leading and Trailing Spaces in Excel

Can I trim spaces in multiple cells at once?

Absolutely, you can trim spaces in a sweeping arc across multiple cells! Just enter the ‘=TRIM()’ formula into the first cell, then click and drag the fill handle (a tiny square at the bottom-right corner of the cell), and watch as Excel casts the spell over the highlighted range. Alternatively, copy the formula, select the range you want to clean, and use ‘Paste Special’ to apply that magic trim en masse.

What to do if TRIM doesn’t remove all excess spaces?

When the TRIM function lets a few spaces slip through the net – don’t fret! This is typically for those sneaky non-breaking spaces. Roll up your sleeves and unleash the ‘SUBSTITUTE’ function to swap these space imposters with regular spaces, then let TRIM do its final sweep. If all else fails, remember, ‘Find & Replace’ is your trusty sidekick, ready to seek out and eliminate any stubborn stragglers that refuse to budge.

 

Conclusion

Using the Trim function, you have learned how to remove trailing spaces in Excel and leading spaces too.

There can be non-breaking spaces in a text as well. To remove them, simply use Substitute Function and replace the non-breaking space with a normal space.

Lastly, if you wish to remove all spaces in Excel the Find & Replace feature would be your best match!

Give these methods a try and become a pro in cleaning data in MS Excel!

If you like this Excel tip, please share it



How to Remove Leading and Trailing Spaces in Excel | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Index Match 2 Criteria with Data Validation

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...