How to remove leading and trailing spaces in Excel cells?

Sometimes, when you copy data from other locations and pasted them at Excel worksheet, there may be some extra spaces leaving at front or the end of strings in cells. It is time-consuming to delete the spaces one by one for making data looks tidy. Here this tutorial introduces the smart and easy ways help you to quickly remove leading and trailing spaces.

• Method A: Remove all extra spaces from strings with the TRIM function (2 steps)
  Remove all excess spaces including extra space, leading and trailing space
• Method B: Remove leading spaces from strings with the VBA code (5 steps)
  Remove only first space, need have a copy before running code
• Method C: Remove leading/trailing/extra/all spaces as you need with Kutools (3 steps)
  Remove leading/trailing/extra/all spaces as you need
• Download sample file
  This sample file contains all data this article need
• Other Operations (Articles) Related To Spaces
  Count space before text string  Find position of nth space So on..

Remove extra spaces from strings

1. Select a cell next to the cell you want to remove extra spaces from string, type this formula

=TRIM(B2)

B2 is the cell you want to remove spaces from, See screenshot:

2. Press Enter key to get the result then drag auto fill handle over the cells you want to remove extra spaces from, all of leading spaces and trailing spaces and extra spaces have been removed.

Tip:

1. You can copy and paste the formula results as value as below screenshot shown:

2. This method is quite easy if you only need to remove spaces in contiguous cells within a row or a column. However, when you need to remove leading spaces in a range with several rows and columns, you have to use the function several times. In this case, you can try method C.

Remove all excess spaces including non-printing characters and non-breaking space

If there are some non-printing characters in your string such as appeared in Excel as CHAR(160), they cannot be removed successfully with the TRIM function. In this case, you need to combine the TRIM function, CLEAN function and SUBSTITUTE function together to handle this job.

Using this formula:

=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))

In this case, Cell B2 is the cell you want to remove all excess spaces.

Then drag fill handle over the cells you want to apply this formula.

13 Text tools You Must Have in Excel Which Will Boot Your Efficiency By 90% ▲ Batch edit text string in cells, such as adding same text to cells at once, remove characters at any position and so on. ▲ Except tools displayed in picture, there are 200 advanced tools else in Kutools for Excel, which can solve your 82% Excel puzzles. ▲ Become an Excel expert in 5 minutes, gain people's recognition and promotion. ▲ 110000+ high efficiency people sand 300+ world renowned companies' choice. 30-days free trial, no credit card require Read More Download Now

If you want to convert or import all data in Word document to Excel, you can use VBA code.

1. Press Alt + F11 key to enable Microsoft Visual Basic for Applications window.

2. Click Insert > Module to create a new Module script, copy and paste below code to the script.

VBA: Deleting leading spaces from string

">Sub RemoveLeadingSpace()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

3. Press F5 key to run the code, then a KutoolsforExcel dialog pops out for selecting cells to remove leading spaces.

4. After choosing cells, click OK, now, the leading spaces in selections have been removed.

This tool is one of 229 tools in Kutools for Excel, it provides five options for you to delete different types of spaces as you need:

• Remove leading spaces
• Remove trailing spaces
• Remove leading and trailing spaces
• Remove all excess spaces
• Remove all spaces

This tool supports Undo, but before you using this utility, you need take minutes to free install it.

1. Select the data you want to remove spaces, then click Kutools > Remove Spaces. See screenshot:

2. Then check the option that you need in the Remove Space dialog.

Remove Leading Spaces only:

Remove Trailing Spaces only:

Remove Leading & Trailing Spaces only:

Remove All Excess Spaces:

Remove All Spaces:

Click to download this sample file