How to Remove Leading Spaces in Excel Cell?
Often in excel, unwanted space characters take a considerable amount of time to clean and make the data perfect. One of the biggest problems with these unwanted leading spaces in excel cell is we cannot simply recognize there is a space in it. This adds to the misery.
#1 – Remove Unwanted Leading Spaces in Excel Cell Using TRIM Function
- Yes, you could argue that why don’t we replace the space with nothing. But the problem is all the necessary spaces are also will get replaced along with it. For example, look at the below sentence in a cell.
In the above sentence, we need one space character after each completed word to make the sentence proper. We have only one extra space after the word capital.
- In order to deal with these unwanted spaces problems, we have a built-in function called TRIM in ExcelTRIM In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces., which can remove all the unwanted spaces in excel cell.
- The syntax is very simple. We just need to select the required cell to be trimmed. The Trim function removes all the spaces except the single space after each word. Let’s apply the Trim formula in excelTrim Formula In ExcelThe Trim function in Excel does exactly what its name implies: it trims some part of any string. The function of this formula is to remove any space in a given string. It does not remove a single space between two words, but it does remove any other unwanted spaces. to remove all kinds of spaces in a cell.
- This trim function removed the spaces between “Capital” and “is.”
#2 – Remove Non-Breaking Leading Spaces in Excel Cell
It is very easy to remove normal leading spaces in the excel cell. In the case of non-breaking leading spaces, the Trim function goes wrong. Look at the below image.
Even after the Trim is applied, we don’t see the perfect sentence here. This is the problem with non-breaking leading spaces in an excel cell.
Non-Breaking Leading Spaces in Excel Cell usually comes when we download the data from a web which is made up of the character CHAR (160). In these cases, we need to use TRIM and SUBSTITUTE excel function together.
Firstly open the TRIM function.
Now apply the SUBSTITUTE function.
We need to select the text now, so select the A4 cell as the reference.
What is the old text we need to remove here? The old text is CHAR (160).
New text to be a substitute is nothing, so mention double quotes (“).
Close the bracket and hit the enter key. We would get an accurate sentence now.
This way, we can deal with breaking spaces in Excel Cell. If the TRIM function is not able to give to proper results, then use this technique.
#3 – Remove Double Spaces Using Excel FIND & REPLACE Method
We can remove double spacesRemove Double SpacesThere are multiple methods to remove spaces in excel - Trim Function, Using Delimited in Text to Columns, Fixed width in Text to Columns, Using Find & Replace Option, and Substitute Function. in cells by using Excel find and replaceExcel Find And ReplaceFind and Replace is an Excel feature that allows you to search for any text, numerical symbol, or special character not just in the current sheet but in the entire workbook. Ctrl+F is the shortcut for find, and Ctrl+H is the shortcut for find and replace. method as well. Assume below is the data we have in excel.
Here we have a first name and last name together. After the first name, we have two spaces instead of one space. In these cases, instead of the TRIM function, we can also use find and replace methods.
Step 1: Select the required area’s double space to be removed.
Step 2: Now press CTRL + H.
You will see below Find & Replace window.
Step 3: In the Find what: box, type two space character.
Step 4: Now, in the Replace with: type only one space character.
Step 5: Now click on Replace All.
Step 6: Now, excel will show you how many replacements it has made.
Step 7: It has removed all the double spaces in the excel cell and replaced it with a single space, and we have proper names now.
#4 – Remove All Leading Spaces Using Excel FIND & REPLACE Method
Assume you are working with numbers, and you have many spaces in between the number,s as shown in the below image.
We have abnormal excel leading spaces in between, before, and after the numbers. In these cases, we need to remove all the spaces. So use the Find & Replace method.
- Step 1: Select the data first.
- Step 2: Press Ctrl + H and type a single space in Find what: box.
- Step 3: Now, don’t enter anything in the replace what: box. Just click on replace all.
- Step 4: It will remove all the leading spaces in the excel cell, and we have numbers in order now.
Things to Remember
- If you are using the FIND & REPLACE method to Remove leading spaces in an excel cell, then it is important to select the range and do the operation.
- If you are downloading the data from the web and you are encountering the same space problem, then you need to use the SUBSTITUTE function to remove CHAR (160) along with the TRIM function.
- In order to remove non-printable characters, use the CLEAN Excel functionCLEAN Excel FunctionClean Function in Excel is a text function that is used to clean the text with the characters that are not printed when we use the print option. To use this function, type =CLEAN( ) in a cell and provide a text as an argument..
This has been a guide to Remove Leading Spaces in Excel Cell. Here we discuss how to Remove Leading Spaces in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –