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.
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.
 |
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. |
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.
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.
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
Click to download this sample file
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.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- 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...
- 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!
