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:

excel-trimclean-20171010-1

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.

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s