Note: The other languages of the website are Google-translated. Back to English

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
Comments (26)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Your new VBA formula causes a compile error! The old one worked fine.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
The first line of the VBA formula produces an error at 1ine 1.
This comment was minimized by the moderator on the site
I figured out the problem. You need to remove the line number 1 from before the word Sub. Then it runs correctly.
This comment was minimized by the moderator on the site
Thank you very much, very very helpful
This comment was minimized by the moderator on the site
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!!!
This comment was minimized by the moderator on the site
This way worked on office 2007 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
This comment was minimized by the moderator on the site
Really i can`t how thank you :)
This comment was minimized by the moderator on the site
Does Kutools work with Powerpoint 2003?
This comment was minimized by the moderator on the site
Thanks for precious info.....
This comment was minimized by the moderator on the site
how do this in simple text editor?
This comment was minimized by the moderator on the site
Quick and easy - thanks!
This comment was minimized by the moderator on the site
Fantastic , Really excellent and wonderful , thanks it solved my problem .
This comment was minimized by the moderator on the site
Thank you for sharing
This comment was minimized by the moderator on the site
Hi Just try replacing the file in Word and then replace all blanks and then copy it back to the excel. it will work:)
This comment was minimized by the moderator on the site
That was fantastic! Thank you for the tip! It didnt even occur to me to use Word!
This comment was minimized by the moderator on the site
So, this is simply a marketing page for a product that has no need to exist?
This comment was minimized by the moderator on the site
Thanks for sharing....
This comment was minimized by the moderator on the site
Its very helpful
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
KUTOOLS is one of the greatest Excel add-ins ever. Great insights above.
This comment was minimized by the moderator on the site
Thanks for your support!!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations