When you are comparing two columns for duplicates that you know are there, but your formulas cannot find a single duplicate entry. Or, when you are adding up two columns of numbers, but keep getting only zeros. When your obviously correct VLOOKUP formula returns just a bunch of N/A errors. These are only some examples of the issues you'll be searching for a solution to. And the whole thing is caused by greater space hidden before, after, or among the numbers and text values in the cell. Fortunately, the numerous functions and capabilities of WPS Spreadsheets help you to manage information efficiently and smartly.

How to remove extra spaces by using the Trim formula?

The TRIM characteristic eliminates leading and trailing spaces and pointless areas between words. The main and trailing spaces are also trimmed to at least one, however no longer eliminated.

· Syntax:

=TRIM (text)

· Arguments:

Text: To delete extra spaces from the text.

1.   First, place the mouse cursor at the cell where we want to display the cleaned result.

2.   Enter =TRIM(A2) in the required cell.

3.   Hover over the cell and drag the small cross in the lower right corner all the way down.

4.   Replace the original column with the column containing the cleaned data. Select all the cells in the help column and press Ctrl + C to copy the data to the clipboard and paste it into the original column with CTRL + V.

It is worth mentioning that the spaces at the start or end of the text can also be addressed by the TRIM function.

How to remove extra spaces by using Find & Replace option?

We have to follow some steps in this option which allows for removing extra spaces in the text. Spaces before and after text will also be trimmed to 1, but will not be removed.

1.   Select one or more columns of data to remove spaces between words.

2.   Press Ctrl + H to display the Find and Replace dialog box.

3.   Press the spacebar twice in the Search string field and once in the Replace string field.

4.   Click the Replace All button and press OK to close the Excel confirmation dialog.

5.   Repeat step 4 until you see the message No replacement found.

Using a formula to remove all spaces

=SUBSTITUTE (A1, ,)

Like a formula chain, you may need to remove all spaces. To do this, create an auxiliary column and enter the following formula:

1.   Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.

2.  Then follow the steps from the part using a formula that removes extra spaces between words to 1.

Did you learn about how to remove spaces after text in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.