Removing Line Returns in Excel

What are Line Returns?

When entering values in a cell in excel, you have the option of using “Alt+Enter” to force shift the data to the next line in the cell. For example, we could write “Unit Volume” as “Unit (Alt+Enter) Volume” and you’ll see what’s different as soon as you hit enter!

When do we use this feature?

Many a time, for formatting purposes only. This can be used identically on values & formulae written in excel. It is especially helpful when used in writing formulae, since it helps in debugging, much like in C or VBA codes. Here’s an example –

Where this helps most is when we have a zillion brackets to account for~

If this is such a nice feature, why do we want to remove it?

Usually, when we copy a set of such text cells, and “paste as values”, there’s a symbol that comes up looks like the one below –

So, if we wanted to reference using this name, or to list the text values elsewhere to front-end purposes, we will have to clear the special character! And if this is the case with a lot of cells, then we need something quick, something automated…

How do we remove this?

There are some ways this can be done –

  • To Manually remove the “Alt-Enters” (painfully slow and error prone)
  • To use the function CLEAN – it removes any unprintable characters in the cells. (So, it removes more than what you’d like)
  • Using SUBSTITUTE(char(10),<>,<>) – char(10) is “Alt-Enter” and this works wonders! (Best Recommended)
  • There’s a particularly unique way to do this, and I have myself never been able to replicate it. “Ctrl+j” is used in Find/Replace for the special character, and voila.. all “Alt-Enters” are removed instantly with a Replace All! (Explanation – Ctrl+j is alternate for Alt+Enter. I got this technique to work just once, so, I’m not very clear how best to do this!)

And there you go.. your worksheet free of line returns.

As an FYI, these also apply to other special characters that may be present in the cells/sheet and one of the above methods will still apply!

Cheers!

Advertisements

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