Occasionally, dates or names won’t merge correctly into a spreadsheet. There are extra spaces, for example, and you may want to get rid of them. How do you clean up your spreadsheets quickly? Use the TRIM and SUBSTITUTE functions. Trim blank spaces and substitute text or blanks with something different and clean up hidden characters!
Here, we can see some extra spaces in between names and in the date cells. The first thing to do is to use the TRIM function.
1. Click in a cell and begin typing =TRIM( followed by clicking on the cell that you want to clean up. Here, for example, A4.
2. Press Enter and copy the formula down by double-clicking the bottom-right of the green outline with the left mouse button.
3. Notice that the extra spaces and the beginning of the dates disappear, but there is still a space between the slash and the day of the month. That is because the TRIM function will leave a single space during the clean-up. The names look perfect with the single space between first and last.
4. To clean up the additional unwanted spaces in the dates, use the SUBSTITUTE function. This time click on the Insert Function button and launch the Function Arguments dialog box.
5. As you can see in the screenshot, the text is A4. Our old text is a single space, so you’ll need to use quotes to create it, as in “ “. The new text will be no space, which will be “”. You’ll substitute the single space with no space to clean up the data in the cell.
6. Click OK and copy the formula down by double-clicking the bottom-right of the green outline with the left mouse button. Notice how ALL spaces have been substituted with no spacing at all. You can see how TRIM is great for cleaning up certain cells and SUBSTITUTE works well in other situations.
7. The final step here is to press Ctrl-C and then Ctrl-V to copy and then paste as values. It is important to strip out the formulas and make the cells constant values.
Like this step-by-step tutorial? We've got a great video, 10 Microsoft Excel Aha Features You Need to Know. Watch the video to see this step-by-step in action and get 9 other great tips tool!
