# 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:
`=TRIM(B5)`

• 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:
`=TRIM(CLEAN(B5))`

• 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:
`=TRIM(CLEAN((SUBSTITUTE(B5,CHAR(160)," "))))`

• 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'
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.

## 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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF