How to Remove Space Before Text in Excel (4 Methods)

How Space Before Text Affects the Dataset?

Space(s) before the text can create a lot of trouble to analyze a dataset. It may give you wrong results that you wouldn’t expect. To clarify, take a look at the following dataset:

Here, we have some spaces before texts. Now, we want to extract the first four characters of a word. We are using the LEFT function to perform this. The output will be like this:

You can see there is a major difference between what we wanted and what we got. Spaces before the text can create this type of problem in formulas.

How to Remove Space Before Text in Excel: 4 Easy Ways

Method 1 – Use of the TRIM and Other Functions to Remove Space Before Text

Case 1.1 Remove Space with the TRIM Function Only

To demonstrate this method, we are using the following dataset. Notice there are some extra spaces not only before the texts but also in between the texts. Our goal is to remove all extra spaces and keep the dataset clean from useless spaces.

Steps 

• Type the following formula in Cell C5:

• Press Enter.

• Drag the Fill Handle icon over the range of cells C6:C9.

Case 1.2 Remove Space with TRIM and CLEAN Functions for Non-Printing Characters

To demonstrate this, we are using the following dataset:

Steps 

• Type the following formula in Cell C5:

• Press Enter.

• Drag the Fill Handle icon over the range of cells C6:C9.

Case 1.3 Delete Spaces with TRIM, CLEAN, and SUBSTITUTE Functions

This method will remove every extra space, line break, and nonbreaking space. To demonstrate this, we are using the following dataset:

Steps 

• Type the following formula in Cell C5:

• Press Enter.

• Drag the Fill Handle icon over the range of cells C6:C9.

Read More: How to Remove White Spaces in Excel

Method 2 – Find & Replace Command to Delete Space Before Text in Excel

To demonstrate this, we are going to use this dataset. There are some unwanted spaces before or in between emails that we will remove.

Steps

• Select all the data.

• Press Ctrl + F on your keyboard to open the Find & Replace dialog box.
• Click on Replace.
• In the Find what box, type a space.

• Keep the Replace with box empty.

• Click on Replace All.

Method 3 – Using VBA to Remove Space Before Text

We are going to use the following dataset to demonstrate:

Steps

• Press Alt + F11 to open the VBA editor.
• Click on Insert, then Module.

• Type the following code and save the file:

Sub remove_space()
Dim search_range As Range
Dim cell As Range
'Taking User Input'
Application.DisplayAlerts = False
On Error Resume Next
Set search_range = Application.Selection
Set search_range = Application.InputBox("Please Enter Your Range of Cells", "Data Range", Type:=8)
On Error GoTo 0
If search_range Is Nothing Then
  Exit Sub
End If
'Loop through cells to run TRIM function'
For Each cell In search_range
  cell.Value = Trim(cell)
Next cell
End Sub

• Press Alt + F8 on your keyboard to open the macro dialog box.
• Select remove_space.
• Click on Run

• Select the range of cells B5:B9. 

• Click on OK after that.

Method 4 – Use Power Query to Remove Space Before Text

To demonstrate this method, we are going to use this dataset:

Steps

• From the Data Tab, select From Table/Range.

• Select the range of cells of your dataset.
• Click on OK.

• The command will launch the power query editor and will look like this.

• Click on Column1 to select everything.

• Right-click and select Transform.

• Click on Trim.

Read More: How to Remove Space in Excel Before Numbers

Things to Remember

• The TRIM function removes extra spaces between texts. If there is more than one space between words, it will reduce that to one space.
• The Find & Replace command will remove every space from the dataset. If your goal is to remove only spaces before the text, don’t use it.

Download Practice Workbook

Download this practice workbook.

Related Articles

• How to Remove Tab Space from Excel
• Remove Space between Rows in Excel
• How to Remove Blank Spaces in Excel
• How to Remove Space after Number in Excel
• How to Find and Replace Space in Excel
• How to Remove Extra Spaces in Excel
• How to Remove Space in Excel after Text
• How to Remove the Trailing Spaces in Excel

<< Go Back To Remove Space in Excel | Data Cleaning in Excel | Learn Excel