Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.
doc remove first space 1


Method A: Remove all extra spaces from strings with the TRIM function (2 steps)

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:
doc remove first space 2

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.
doc remove first space 3

Tip:

1. You can copy and paste the formula results as value as below screenshot shown:
doc remove first space 4

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.
doc remove first space 5

Then drag fill handle over the cells you want to apply this formula.
doc remove first space 6

Note:

The TRIM function will remove all extra spaces including leading space, trailing spaces and the extra spaces between words. If you want to remove leading spaces only, please go to Method B. If you want to remove leading spaces only, or trailing spaces only, or want to remove all excess spaces including non-printing characters, please go to method C.


doc text tools

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.

60-days free trial, no credit card require


Method B: Remove leading spaces from strings with the VBA code (5 steps)

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

doc remove first space 7 doc remove first space 8

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

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

doc remove first space 10

Note:

With this VBA code, it removes leading spaces only, and it will change original data and does not support Undo. Before you applying this VBA code, please save a copy of your data.


Method C: Remove leading/trailing/extra/all spaces as you need with Kutools (3 steps)

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:
doc remove first space 11

2. Then check the option that you need in the Remove Space dialog.
doc remove first space 12

Remove Leading Spaces only:
doc remove first space 13

Remove Trailing Spaces only:
doc remove first space 14

Remove Leading & Trailing Spaces only:
doc remove first space 15

Remove All Excess Spaces:
doc remove first space 16

Remove All Spaces:
doc remove first space 17

Demo: Remove Spaces


Download sample file

doc sampleClick to download this sample file


Other Operations (Articles) Related To File Conversion

Count space before text string
Provides a trick to quickly count the number of leading spaces

Find position of nth space
In this article, it introduces the tricks on easily find the position of nth space in Excel.

Add space between first name and last name
In general, there is a space between first name and last name. But in some times, the spaces are lost so that you need to add space between names in Excel. Here will provide two different ways to quickly handle this job.

Count number of characters exclduing spaces
Usually you use the LEN function to count number of characters, however, if you want to count characters excluding space, you need another formula. In this article, it provides two easy ways for you to choose to handle this puzzels.



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    narendra · 8 months ago
    awesome sir...
  • To post as a guest, your comment is unpublished.
    Angel One · 9 months ago
    Excelente aportación
  • To post as a guest, your comment is unpublished.
    Koala · 9 months ago
    THANKS GUYS SAVED MY LIFE!!!!!
  • To post as a guest, your comment is unpublished.
    Daniel Musyoka · 1 years ago
    KUTOOLS is one of the greatest Excel add-ins ever. Great insights above.
  • To post as a guest, your comment is unpublished.
    Sam Ried · 1 years ago
    Thanks for this wonderful article! hats off to your writing! great post with rich quality content! Very resourceful and informative! Keep posting! Would love to follow up on your upcoming future posts! Ninja Heroes Mod Apk
  • To post as a guest, your comment is unpublished.
    Shafiq · 1 years ago
    Its very helpful
  • To post as a guest, your comment is unpublished.
    win htet maung · 2 years ago
    Thanks for sharing....
  • To post as a guest, your comment is unpublished.
    yermum · 2 years ago
    So, this is simply a marketing page for a product that has no need to exist?
  • To post as a guest, your comment is unpublished.
    Sahar · 2 years ago
    Hi Just try replacing the file in Word and then replace all blanks and then copy it back to the excel.
    it will work:)
    • To post as a guest, your comment is unpublished.
      Sarah · 2 years ago
      That was fantastic! Thank you for the tip! It didnt even occur to me to use Word!
  • To post as a guest, your comment is unpublished.
    John · 2 years ago
    Thank you for sharing
  • To post as a guest, your comment is unpublished.
    Som · 2 years ago
    Fantastic , Really excellent and wonderful , thanks it solved my problem .
  • To post as a guest, your comment is unpublished.
    TIRED · 3 years ago
    Quick and easy - thanks!
  • To post as a guest, your comment is unpublished.
    niepelnosprawny,org · 3 years ago
    how do this in simple text editor?
  • To post as a guest, your comment is unpublished.
    Haider` · 3 years ago
    Thanks for precious info.....
  • To post as a guest, your comment is unpublished.
    Retired InPeace · 3 years ago
    Does Kutools work with Powerpoint 2003?
  • To post as a guest, your comment is unpublished.
    Hunpy · 3 years ago
    Really i can`t how thank you :)
  • To post as a guest, your comment is unpublished.
    alexander · 4 years ago
    [b]This way worked on office 2007[/b]

    Sub RemoveLeadingSpace()
    'Updateby20131129
    Dim Rng As Range
    Dim WorkRng As Range
    Dim temp as String
    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
    temp = VBA.Trim(Rng.Value)
    Rng.Value = temp
    Next
    End Sub
  • To post as a guest, your comment is unpublished.
    Sandi · 4 years ago
    Yours was the first one to pop up on Google when I needed to remove leading spaces in excel, and it did the trick! Thanks!!!
  • To post as a guest, your comment is unpublished.
    Luka Moderna · 4 years ago
    Thank you very much, very very helpful
  • To post as a guest, your comment is unpublished.
    NachoMama · 4 years ago
    I figured out the problem. You need to remove the line number 1 from before the word Sub. Then it runs correctly.
  • To post as a guest, your comment is unpublished.
    NachoMama · 4 years ago
    The first line of the VBA formula produces an error at 1ine 1.
  • To post as a guest, your comment is unpublished.
    DON · 5 years ago
    Fantastic Explanation of how to remove spaces. Kutools is such a magic tool. Thanx for providing the download link widot any gimmick and giving so much vital and accurate info....will be a regular visitor now
  • To post as a guest, your comment is unpublished.
    Guest · 5 years ago
    Your new VBA formula causes a compile error! The old one worked fine.