How to Remove Spaces Using SUBSTITUTE Function
Step 1: Enter Data in the Excel Sheet
Now, to use the formula, we need to make a temporary column for the correct data at the end of the spreadsheet. So we have taken a data and temporary column called “Temp” similar to the first case.
Step 2: Select a Cell and Apply the Substitute Formula
Again select the first cell of the Temp column then go to the FORMULAS tab and then select the Text dropdown menu and click on SUBSTITUTE.
Step 3: Functional Argument Box Popped Up
Afterward, a dialogue box will appear. Now enter the cell where we need to remove spaces in the Text Field. Then put ” “ in the ‘Old_text’ field and enter “” In the ‘New_text’ field and then press OK.
Step 4: Extra Blank Spaces Removed
So, now the extra spaces in cell A2 get removed and it will be reflected in the new column Temp. Use the same function to remove all the extra spaces in the column Name.
Step 5: Copy Temp, Paste in Name, and Delete Temporary Column
Afterward, just copy the column Temp paste all the data in the Name, and delete your temporary column.
How to Remove Spaces in Excel
Have you ever wondered you keep searching data on Excel but it is not returning any values and many times you are trying to add columns or data but you are unable to do so? Well, these are some common problems we all have faced in our life. But do you know why it happens? This happens because of extra space(hidden) that remains in the text or number of your data.
How to Remove Spaces in Excel
By the end of this article, you’ll know how to make your Excel data look neater and work better using simple functions and tools like TRIM, Find and Replace, and more.