Excel: Trimming Cell Entries
A friend recently complained to me about mail merge results she was getting from her Excel data. Her spreadsheet held lists of important donors to her organization, but the data had been entered in various ways from many different sources. As a result, cells in the data held random spaces before or after the information causing her address labels and letter components to look less than professional. Excel’s Trim function came to her rescue. The Trim function removes spaces from text.
My friend’s original data looked something like this:
There are extra spaces in front of “Ralph” and “Jones” for example. In these two records it would be easy to just manually remove those spaces, but my friend was working with many thousands of records. Another less evident problem in the data was that there were also random spaces after the text that interfered with placement of this text in other documents.
Our solution was to first insert a new column that would hold the trimmed text to the right of each column that needed spaces removed. We then placed the trim function in the cells of that new column. So if “Ralph” is sitting in cell A2, the newly-added cell B2 held this formula: =TRIM(A2).
We then hid the original columns that held extra spaces, selected all columns, used ALT + ; to limit the selection to just the visible cells, then copied and pasted the cleaned information to a new sheet.
Data with spaces removed - ready to be merged
