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.
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
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:Â
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:
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)
â‘¡ Then, press Enter.
â‘¢ After that drag the Fill Handle icon over the range of cells C6:C9.
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:
📌 StepsÂ
â‘ First, type the following formula in Cell C5:
=TRIM(CLEAN(B5))
â‘¡ Then, press Enter.
â‘¢ After that, drag the Fill Handle icon over the range of cells C6:C9.
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:
📌 StepsÂ
â‘ First, type the following formula in Cell C5:
=TRIM(CLEAN((SUBSTITUTE(B5,CHAR(160)," "))))
â‘¡ Then, press Enter.
â‘¢ After that drag the Fill Handle icon over the range of cells C6:C9.
As you can see, there are no spaces or nonbreaking spaces before text.
Read More: How to Remove White Spaces 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:
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.
â‘¡ 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.
â‘£ Now, keep the Replace with box empty.
⑤ Click on Replace All.
As you can see, we are successful in removing all the useless spaces from the texts.
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:
📌 Steps
â‘ First, press Alt+F11 to open the VBA editor. Click on Insert > 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
â‘£ Select the range of cells B5:B9.Â
⑤ Click on OK after that.
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:
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 the range of cells of your dataset. Click on OK.
â‘¢ After that, it will launch the power query editor and will look like this.
â‘£ Click on Column1 to select every text.
⑤ Then right-click on the mouse. Select Transform.
â‘¥ After that click on TRIM.
As you can see, we have removed all the leading spaces from the dataset successfully.
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, 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.
Download Practice Workbook
Download this practice workbook.
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.
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
