Method 1 – Using the TRIM Function to Remove Leading Spaces
Steps:
- Select the cell where you want to place your resultant value. (Cell E4.)
- Enter the following formula in the cell or into the Formula Bar:
=TRIM(D4)
- Press ENTER. It will show the result by removing all extra spaces.
- You can use the Fill Handle to AutoFill formula for the rest of the cells.
1.1 Removing Leading Spaces from Text Values
Steps:
- Select the cell where you want to place your result.(Cell E4)
- Enter the following formula in the cell or into the Formula Bar:
=MID(D4,FIND(MID(TRIM(D4),1,1),D4),LEN(D4))
Here, I’ve inserted D4 as the value where I wanted to remove spaces.
The FIND portion will detect the position of the first text character string, and the LEN function will count the string length that is to be extracted through the outer MID function.
The FIND portion will detect the position of the first text character string, and the LEN function will count the string length that is to be extracted through the outer MID function.
- Press ENTER.
It will show the result by removing only the leading spaces of a selected cell.
- You can use the Fill Handle to AutoFill formula for the rest of the cells.
Read More: How to Remove Space Before Text in Excel
1.2 Removal of Leading Spaces from Numeric Values
Steps:
- Select the cell where you want to place your resultant value. (cell D4.)
- Enter the formula in the cell or into the Formula Bar:
=VALUE(TRIM(C4))
Here, the TRIM function removes the leading spaces from the numeric value in C4. Then VALUE converts it into the Number Format.
- Press ENTER.
It will remove the leading spaces of the numeric value and will convert it into a number format.
- You can use the Fill Handle to AutoFill formula for the rest of the cells.
Read More: How to Remove Space in Excel Before Numbers
Method 2 – Using TRIM within SUBSTITUTE to Remove Non-breaking Leading Spaces
Steps:
- Select the cell where you want to place your resultant value. (cell C4)
- Enter the following formula in the cell or into the Formula Bar:
=TRIM(SUBSTITUTE(B4, CHAR(160), ""))
Here, the SUBSTITUTE function will substitute all instances of CHAR(160) with normal space characters from the B4 cell. The TRIM function will remove those spaces.
- Press the ENTER key. It will show the result by removing all non-breaking spaces.
- Use the Fill Handle to AutoFill formula for the rest of the cells.
Read More: How to Remove White Space in Excel
Method 3 – Using FIND and REPLACE Tool to Remove Leading Spaces
3.1. Removal of Double Leading Spaces
Steps:
- Select the cell from where you want to remove the leading spaces.
- Go to the Home tab.
- Go to Find & Select >> Replace…
- A dialog box will pop up. Enter how many spaces you want to remove in Find what. (I gave double spaces.)
- Enter how many spaces you want to replace the existing spaces in Replace with. (I gave single spaces.)
- Click Replace All.
- A message will pop up showing how many replacements occurred.
- It will show 8 replacements. If there are more leading spaces in your selected cell, you can click on Replace All again. It will remove the existing leading spaces. Again, it replaced 7 spaces.
- All the leading spaces are replaced with a single space.
3.2. Removal of Single Leading Spaces
Steps:
- Select the cells from where you want to remove leading single spaces.
- Open the Find & Replace dialogue box following the steps shown in the previous section.
- Insert a single space in the Find what and keep the Replace with blank.
- Click on Replace ALL. A message will pop up showing how many replacements occurred.
- Click OK. It will show 24 replacements.
- All the single spaces are replaced by no space.
Read More: How to Remove Blank Spaces in Excel
Method 4 – Using VBA for Removing Leading Spaces
Steps:
- Open the Developer tab.
- Select Visual Basic.
- Microsoft Visual Basic for Applications will open.
- Open Insert >> Module.
The Module is open.
- Enter the code to remove leading spaces in the Module:
Sub Remove_Leading_Space()
Dim Rng As Range
Dim SelectedRng As Range
On Error Resume Next
ExcelTitleId = "Removing Leading Space"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Range", ExcelTitleId, SelectedRng.Address, Type:=8)
For Each Rng In SelectedRng
Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub
Here, I’ve taken two variables, Rng and SelectedRng, named the dialog box Removing Leading Space, and used a for loop to TRIM each selected cell.
I’ve used the VBA LTRIM function to trim.
I’ve used the VBA LTRIM function to trim.
- Save the code and go back to the worksheet.
- Select the cell or cell range to apply the VBA.
- Type Alt+F8 to open the Macro window.
- Select the Macro and click Run.
- A dialog box will pop up showing the selected range. Click OK.
- The leading spaces of the selected cell range will be removed.
Note: A VBA code will remove the leading spaces only in the selected cell range, and it will change original data and does not support Undo. Furthermore, save a copy of your data before applying this VBA code.
Read More: How to Remove Space in Excel after Text
Practice Section
Here is a practice sheet from the workbook to practice.
Download the Practice Workbook
Related Articles
- How to Remove the Trailing Spaces in Excel
- Remove Space after Number in Excel
- How to Remove Space between Rows in Excel
- Remove Tab Space from Excel
- How to Find and Replace Space in Excel
- How to Remove Extra Spaces in Excel
<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Great tips! I never knew about the TRIM function until now. It’s going to save me so much time when cleaning up my spreadsheets. Thanks for sharing!
Hello,
You are most welcome. Thanks for your feedback and appreciation. Glad to hear that you got to know about TRIM function from our article and it saved your so much time. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy