- slide 1 of 2
If the text you imported, pasted, or otherwise received in an Excel spreadsheet possesses some distracting spaces, the TRIM function will help you get rid of them. Aside from looking slightly unprofessional, these spaces can get in your way when you attempt to find duplicate items in your data. This is because incorrect spaces between data will skew the results.
- slide 2 of 2
Get Rid of Blank Spaces
The syntax for Excel’s TRIM function is TRIM(text). This means that when you enter the TRIM function into a cell, you will need to refer to the text you want to extract and trim from another location in the spreadsheet.
In our example, we have some randomly generated text (I don’t think it is a real language, but I may be wrong!). This text has sporadic extra spacing that we want to remove.
We will select a cell into which we want the first piece of trimmed text extracted. In the Formula Bar, we will enter =TRIM( and Excel will prompt us for the text we want to trim.
Click to select the cell you want trimmed and extracted. In this case, we will select cell A1. Press Enter. The formula for the selected cell now reads =TRIM(A1) and the text has all of the extra spacing removed.
Once you have entered the formula, select the first cell containing the function. Grab the fill handle and drag the formula down to the rest of the column to copy the formula. If you do not want users to view the original column with the over spaced text, select that column, right click, and choose Hide. The text in the cells to which you applied the TRIM function will still be visible, but the original will be safely out of sight.
Another helpful tip to know when using Excel’s TRIM function is that it can be used in accordance with other text functions. For example, we will replace the original formula in the first cell of our sample spreadsheet with
We now have the extracted text, with the extra spaces trimmed and in uppercase.
If you want to make it even easier to trim that text, you might want to try an add-in to do more of the work for you. Sound interesting? Check out the BrightHub article, Trim Spaces Plug In for Microsoft Excel to find out more about it.