Skip to main content

Excel Remove Spaces: Leading, Trailing, Extra or All Spaces

When importing data from external sources like the web into Excel, you might encounter unwanted leading, trailing, or extra spaces between words and numbers. Such spaces can lead to calculation errors and disrupt data analysis. This guide will introduce several methods to efficiently eliminate these superfluous spaces, ensuring the accuracy and integrity of your data.


Video: Remove Spaces

 


Remove leading, trailing, extra spaces between words or numbers

 

Working with text data, such as names, you might often encounter unnecessary spaces. Let's look at how to get rid of these.


Remove leading, trailing, extra spaces between words or numbers by TRIM function

Excel's TRIM function is designed to remove leading, trailing and extra spaces in a text string. Here's how to use it.

Step 1: Select a cell and use TRIM function

If you want to remove the leading, trailing spaces and extra spaces between words in cell A4, in an empty cell, use the TRIM function, then press Enter key.

=TRIM(A4)

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells, here we drag to cell B8.

Notes:
  • To replace the formula results with calculated values, first select the formula cells and press Ctrl + C. Then, right-click on the same selected cells, and under the Paste Options section in the context menu, choose Values.
  • TRIM function also can remove line breaks in the text string.
  • TRIM function doesn't remove non-breaking spaces (  or Char(160)). To deal with these, please refer to the Remove non-breaking spaces section.
  • TRIM function doesn't remove non-printable characters. To deal with these, please refer to the Remove non-printable characters section.

Remove leading, trailing, extra spaces between words by a versatile text tool with few clicks

Compared to the TRIM function, I highly recommend the Remove Spaces tool from Kutools for Excel. It's a game-changer: no need for complex formulas, offers flexibility in removing various spaces, handles batch selections, and even tackles non-breaking spaces.

Select the cells you want to handle, click Kutools > Text > Remove Spaces, choose one option as you need, and preview the result in the right section, and then click OK. Here I choose All excess spaces option to remove leading, trailing and extra spaces between words.

Note: If you haven't experienced the magic of Kutools for Excel yet, it's about time you do. Download it now and enjoy a 30-day free trial, no limitations attached. Discover an Excel experience like no other.

Remove all spaces between numbers or characters

Removing spaces between numbers (including leading spaces and trailing spaces) is crucial to avoid calculation errors. Fortunately, you can effortlessly achieve this with the following methods:


Remove all spaces by Find and Replace feature

The Find and Replace feature in Excel is a quick, formula-free way to remove all spaces.

Step 1: Select the cells you want to remove all spaces

Step 2: Press Ctrl + H to display Find and Replace dialog

You also can find this feature in the ribbon by clicking Home > Find & Select > Replace.

Step 3: Replace spaces with nothing in the Find and Replace dialog

In the Find and Replace dialog, set as follows:

  1. Type a space into the Find what textbox.
  2. Leave nothing in the Replace with textbox.
  3. Click Replace All.
  4. Click OK to finish.
Result

doc remove space 9 1

Effortlessly remove all spaces in Excel with Kutools for Excel's powerful Remove Spaces feature. Save time and achieve precise results in just a few clicks. Download now for enhanced productivity!   


Remove all spaces by SUBSTITUTE function

If you want to remove all spaces but preserve the original data, you can use the SUBSTITUTE function to obtain the data without any spaces in a new column.

Step 1: Select a cell and use SUBSTITUTE function

To remove the spaces in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

=SUBSTITUTE(A4," ","")

Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.


Remove non-breaking spaces ( )

When importing data from other sources, you might encounter non-breaking spaces such as html character   which is not removed by using TRIM function in Excel, you can use a formula that combine TRIM and SUBSTITUTE functions to remove extra spaces including non-breaking spaces.

Step 1: Select a cell and use formula

To remove extra spaces including non-breaking spaces in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

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

  • A non-breaking character is represented by the value 160 in the 7-bit ASCII system. You can easily define it using the CHAR(160) formula.
  • SUBSTITUTE function replaces non-breaking spaces with normal spaces.
  • TRIM function is used to remove all extra normal spaces.
Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.


Remove non-printable characters

 

Some data imported may contain extra spaces and non-printable characters (ASCII code 0-31). Here's how to remove them:

Step 1: Select a cell and use formula

To remove extra spaces and the non-printable characters in cell A4, please use the below formula in any empty cell, then press Enter key to get the first result.

=TRIM(CLEAN(A4))

  • CLEAN function is used to clean the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31).
  • TRIM function is used to remove all extra normal spaces.
Step 2: Apply the formula to other cells and get the results

After getting the first result, drag the fill handle of the formula cell (B4 in this example) down to apply the formula to other cells.

Tips:
  • The limitation of the above formula: In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). For these additional non-printable characters, the CLEAN function does not remove by itself.
  • If you want to remove extra space, non-breaking spaces and nonprinting characters, please use this formula:
    =TRIM(CLEAN(SUBSTITUTE(A4,CHAR(160)," ")))

Want a pristine spreadsheet free from pesky non-printing characters? With the Remove Characters feature of Kutools for Excel, not only can you swiftly eliminate these nuisances, but you can also target and remove any specific character types. Experience the magic firsthand - download now for free and elevate your Excel game!   

The information provided above details how to remove spaces in Excel. I hope you find it valuable and beneficial. For more invaluable Excel tips and tricks that can transform your data processing, dive in here.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations