While typing in Excel, they have double spaces between the cells. The double spaces may increase the number of words in an Excel document. Therefore, data cleaning is essential to remove these excess spaces. This article will discuss several ways that can be used to find and remove blank space.
Using Find and Replace Tool
A] Using the Home Tab
Steps:
1. Open the Excel application.
2. Highlight the cells with the dataset you need to find and remove spaces.
3. Click on the Home tab on the ribbon, and locate the Editing section.
4. In the Editing section, click the Find & Select drop-down button and select the Replace option.
5. Click on the Replace tab in the Find and Replace dialogue box. Press the Space Bar twice in the Find What section, and press the Space Bar once in the Replace with Section.
6. Click the Replace All button to find and remove all double spaces in the selected sheet.
B] Using a Keyboard shortcut
Keyboard shortcuts play a vital role in easing tasks in Excel.
Steps to follow:
1. Open the Excel application.
2. Highlight the cells with the dataset you need to find and remove spaces.
3. Press CTRL + H keys to open the Find and Replace dialogue box.
4. Click on the Replace tab in the Find and Replace dialogue box. Press the Space Bar twice in the Find What section, and press the Space Bar once in the Replace with Section.
5. Click the Replace All button to find and remove all double spaces in the selected sheet.
Using the TRIM Function to find and remove spaces
A] Using the Formula bar
Here are the steps to follow:
1. Open the Excel application.
2. Open the Workbook that contains your dataset or open a new worksheet.
3. Click on an empty cell that will hold the results, and then click on the Formula bar.
4. Type the Equal sign followed by the TRIM function. That is, =TRIM (
5. Select the cells with your argument (text string). For Example, =TRIM (A1)
6. Finally, hit the Enter key. That is all you need to do. The double spaces will be removed, and the clean data will be stored in the selected cell.
B] Using the Insert Function Tool
Steps to follow:
1. Open the Excel application.
2. Open the Workbook that contains your dataset or open a new worksheet.
3. Click on the Formulas tab on the screen, and then locate the Function Library section.
4. Click the Insert Function button to open the Insert Function dialogue box.
5. In the Search for a Function section, type TRIM, and press the Go button.
6. Select the TRIM option in the Select a Function section and then hit the Ok button.
7. In the Text section, click on the button on the right side, and select the cell that contains the string you want to clean.
8. Finally, click the OK button. That is all you need to do. The double spaces will be removed, and the clean data will be stored in the selected cell.