How to remove space in Excel after text
By
SpreadCheaters
You can watch a video tutorial here.
When you import data from an external source, very often you need to spend time cleaning up the data before it can be analyzed or summarized. One big problem with such data is the presence of unwanted spaces. Since Excel looks for exact matches when consolidating data, the presence of additional spaces, known as whitespaces, can give you false results.
Step 1 – Use the TRIM() function in the adjoining column
– In the column adjoining the column with the data, enter the formula using the cell reference:
=TRIM(Name)
– Press Enter
Step 2 – Copy the formula
– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
Step 3 – Use the LEN() function to check that extra spaces have been removed
– In the column adjoining the column with the data, enter the formula using the cell reference:
=LEN(Trimmed Name)
– Press Enter
– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a) Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b) Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
– Check that the lengths of the text are correct
