How to remove leading spaces in cells in Excel?

Sometimes there are some blank spaces before strings in cells, these spaces are called leading spaces. It must be arduous to remove leading spaces in cells one by one. Actually, there are several smart tricks that can help us remove leading spaces in cells conveniently and quickly.

Remove leading spaces in cells with Trim function

Remove leading spaces in cells with VBA

Remove leading spaces in cells with Kutools for Excel

Suppose we paste some content from Microsoft Word with indent spaces, then it shows the leading spaces in each cell as the following screenshot shows.


arrow blue right bubble Remove leading spaces in cells with Trim function


The Trim function in Microsoft Excel can remove all spaces from text except for single spaces between words.

Step 1: Enter the formula =TRIM (A1) in the adjacent cell C1 and press the Enter key.

Step 2: Select cell C1 and drag the fill handle down to the range cell that you want to remove the leading space. Then you can see all cell content are extracted with all leading spaces removed. See screenshot:

You can replace the original content with the extracted ones by copying and pasting them as values.

This method is quite easy if you only need to remove leading 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.


arrow blue right bubble Remove leading spaces in cells with VBA

If you are experienced with Microsoft Excel,  VBA macro is a better choice to remove the leading spaces in cells.

Step 1: Hold down the Alt + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

Step 2: Click Insert > Module, and paste the following macro in the Module window.

VBA: Remove leading spaces:

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

Step 3: Press the F5 key to run this macro. In the pop-up KutoolsforExcel dialog box, you need to select a range with leading space you want to remove, and then click the OK button. See screenshot:

Then you can see all leading spaces are moved from the selected range, see screenshots:


arrow blue right bubble Remove leading spaces in cells with Kutools for Excel

If you have Kutools for Excel installed, you can remove all leading spaces of cells in a selection conveniently and quickly.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

Step 1: Select the range which you want to remove all leading spaces in cells.

Step 2: Click the Kutools > Text > Remove Spaces. See screenshot:

Step 3: In the Remove Spaces dialog box, check the Leading Spaces option in Spaces Type section, and then click the OK or Apply button. See screenshot:

Now all leading spaces are removed from the selected cells.

Kutools for Excel's Remove Spaces tool makes it possible to delete all kinds of spaces from strings in cells, including all extra spaces, leading spaces, trailing spaces, or all spaces. Click to know more...


arrow blue right bubble Remove leading spaces in cells with Kutools for Excel

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Related articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink -3 Guest
Your new VBA formula causes a compile error! The old one worked fine.
2013-12-09 13:55 Reply Reply with quote Quote
Permalink +3 DON
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
2014-08-27 11:45 Reply Reply with quote Quote
Permalink 0 NachoMama
The first line of the VBA formula produces an error at 1ine 1.
2015-07-20 21:19 Reply Reply with quote Quote
Permalink 0 NachoMama
I figured out the problem. You need to remove the line number 1 from before the word Sub. Then it runs correctly.
2015-07-20 21:27 Reply Reply with quote Quote
Permalink 0 Luka Moderna
Thank you very much, very very helpful
2015-07-23 09:21 Reply Reply with quote Quote
Permalink 0 Sandi
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!!!
2015-07-31 13:06 Reply Reply with quote Quote
Permalink 0 alexander
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.Inp utBox("Range", xTitleId, WorkRng.Address , Type:=8)
For Each Rng In WorkRng
temp = VBA.Trim(Rng.Va lue)
Rng.Value = temp
Next
End Sub
2015-09-25 23:58 Reply Reply with quote Quote
Permalink +1 Hunpy
Really i can`t how thank you :)
2015-11-23 12:31 Reply Reply with quote Quote
Permalink +1 Retired InPeace
Does Kutools work with Powerpoint 2003?
2016-01-16 01:37 Reply Reply with quote Quote
Permalink 0 Haider`
Thanks for precious info.....
2016-02-04 14:07 Reply Reply with quote Quote
Permalink 0 niepelnosprawny,org
how do this in simple text editor?
2016-06-11 18:42 Reply Reply with quote Quote
Permalink 0 TIRED
Quick and easy - thanks!
2016-08-15 20:44 Reply Reply with quote Quote
Permalink 0 Som
Fantastic , Really excellent and wonderful , thanks it solved my problem .
2016-12-01 09:08 Reply Reply with quote Quote
Permalink 0 John
Thank you for sharing
2017-02-15 17:38 Reply Reply with quote Quote
Permalink 0 Sahar
Hi Just try replacing the file in Word and then replace all blanks and then copy it back to the excel.
it will work:)
2017-07-12 00:20 Reply Reply with quote Quote

Add comment


Security code
Refresh