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.
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.
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.
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!