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

When we deal with text in Excel, spaces between text are a common scenario. Spaces are necessary for datasets. But, extra spaces may cause miscalculations or misinterpretations of a dataset. That’s why it is essential to remove those spaces before and between text. In this tutorial, you will learn how to remove space before the text in Excel with suitable examples and proper illustrations.


Download Practice Workbook

Download this practice workbook.


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:

dataset to show of affects of space before text in excel

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:

formulas behave differently because of space

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.


4 Ways to Remove Space Before Text in Excel

In the upcoming sections, we will provide you with four methods that you can use to remove space before the text in excel. We recommend you learn them all and apply the one suitable for you.

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

Now, we are using the TRIM function to remove spaces before the text. It is the go-to method for this kind of problem.

The TRIM function removes all spaces from a text string except for single spaces between words.

Syntax: 

=TRIM(Text)

Text: The text from where you want to remove space.

1.1 Remove Space with the TRIM Function Only

To demonstrate this method, we are using the following dataset:

dataset on remove space before text in excel

Notice here. 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 

First, type the following formula in Cell C5:

=TRIM(B5)

trim function to remove space in excel

Then, press Enter.

result of trim function

After that drag the Fill Handle icon over the range of cells C6:C9.

result of remove space before text in excel

As you can see, we are successful in removing space before text also between text.

1.2 Remove Space with TRIM and CLEAN Functions

The TRIM function only deletes the space character that is code value 32 in the 7-bit ASCII character set.
There is one more space character called the non-breaking space, which is commonly used on web pages as the HTML character. The CLEAN function also removes non-printing characters like line breaks.

To demonstrate this, we are using the following dataset:

dataset for remove space before text in excel

📌 Steps 

First, type the following formula in Cell C5:

=TRIM(CLEAN(B5))

trim and clean function to remove space in excel

Then, press Enter.

result of trim and clean function in excel

After that, drag the Fill Handle icon over the range of cells C6:C9.

No space or line breaks before text in excel

Now you can see that there are no spaces or line breaks before the text.

1.3 Delete Space with TRIM, CLEAN, and SUBSTITUTE  Functions

There are nonbreaking spaces that have a decimal value of 160, and the TRIM function cannot remove it by itself. If your data set contains one or more white spaces that the TRIM function does not remove, use the SUBSTITUTE function to convert non-breaking spaces into regular spaces and then trim that.
This method will remove every extra space, line break, and nonbreaking space. To demonstrate this, we are using the following dataset:

dataset to remove space before text in excel

📌 Steps 

First, type the following formula in Cell C5:

=TRIM(CLEAN((SUBSTITUTE(B5,CHAR(160)," "))))

trim, clean and substitute formula to remove space

Then, press Enter.

result of the formula

After that drag the Fill Handle icon over the range of cells C6:C9.

No space before text in excel

As you can see, there are no spaces or nonbreaking spaces before text.

Read more: How to Remove Spaces in a Cell in Excel

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

Now, this method is useful if you want to remove useless spaces before or in between the texts. This method is faster than others. If your goal is to remove all the spaces, this method will work at ease.

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

dataset to remove space before text in excel

Here you can see some emails. There are some unwanted spaces before or in between them. We are going to remove all of these spaces by this method.

📌 Steps

Firstly, select all the data.

select all the data to remove space

Then, press Ctrl+F on your keyboard to open the Find & Replace dialog box.

Click on Replace. Then, in the Find what box, type a Space.

find and replace dialog box

Now, keep the Replace with box empty.

replace space with nothing in replace with box

Click on Replace All.

No space before text in excel

As you can see, we are successful in removing all the useless spaces from the texts.


Similar Readings:


3. Using VBA to Remove Space Before Text

Lastly, we are going to provide you with a piece of VBA Macros to remove the useless disturbing spaces within the text.

We are going to use the following dataset to demonstrate:

dataset to implement VBA code in excel to remove space

📌 Steps

First, press Alt+F11 to open the VBA editor. Click on Insert > Module.

insert VBA 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

Now, press Alt+F8 on your keyboard to open the macro dialog box.

Select remove_space. Click on Run

macro dialog box to remove space before text in excel

Select the range of cells B5:B9. 

select range of cells to remove space in excel

Click on OK after that.

no space before text in excel

As you can see, we have removed all the spaces before the text using the VBA code.

4. Power Query to Remove Space Before Text

You can also remove spaces from using the Power Query tool in Excel. There is a built-in TRIM feature in Power Query.

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

dataset to remove space before text in excel

As you can see, there are some spaces before the data. We are going to remove that using the Power Query feature.

📌 Steps

From the Data Tab, select From Table/Range.

select From table/range from data tab

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

select range of cells to remove space before text in excel

After that, it will launch the power query editor and will look like this.

power query data

Click on Column1 to select every text.

click on column1 to remove space before text in excel

Then right-click on the mouse. Select Transform.

select transform and click TRIM

After that click on TRIM.

remove space before text in excel

As you can see, we have removed all the leading spaces from the dataset successfully.


💬 Things to Remember

✎ The TRIM function removes extra spaces between texts. If there is more than one space, it will bring them into one space.

✎ The Find & Replace command will remove every space from the dataset. If your goal is to remove space before the text, don’t use it.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to remove space before the text in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo