Cleaning spaces and line feeds in Excel

A while back, a colleague pinged me and asked if there was a way to remove line feeds from an Excel spreadsheet. I didn’t know of a good way to do it, but I came up with a hack-ish solution using Find and Replace.  Unfortunately, I didn’t know about the Trim and Clean functions at the time:

In cell A1, I have some text that has extra spaces and some line feeds. In cell A2, I have the formula =TRIM(CLEAN(A1)). When I execute that formula, you see where the extra spaces are removed, as well as the line feeds.

The TRIM function is responsible for removing extra spaces. It will trim spaces at the beginning or end of a text string, as well as multiple spaces together in the middle of the string.

The CLEAN function removes unprintable characters in a cell. This includes items such as line feeds.