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
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.
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:
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.
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.
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.
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.
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:
Demo: Remove Spaces
|Kutools for Excel: hundreds of advanced functions you must have in Excel, 60-day free trial from here.|