Remove Line Break instantly

Two different ways to replace Line Break with a space QUICKLY

If you are trying to build a table of data which is analysis-friendly, DO NOT use line break in your fields and labels.  Otherwise, you will create unnecessary steps to remove line break for  effective lookups related formula… Sounds familiar to you? ;p

Fortunate enough, it’s not  difficult to remove all line breaks in your spreadsheet:

Using Find and Replace

  1. Select the range you want to find the line break (unless you want to find the whole worksheet)
  2. CTRL H to open the Find and Replace dialogue box
  3. Click into the Find what: and then press CTRL J (you may see nothing; but when you pay attention to it, you will see the tiny blinking dot at the bottom.  See screenshot below)Excel Tips - Find Replace Line Break
  4. Replace with: Either a single space or nothing. (depends on your original data)
  5. Replace All

Using Formula


Excel Tips - Find Replace Line Break 1

Literally it means

Look at the content in cell A2, look for Line Break, then replace it with an empty space.

CHAR(10) means Line Break.

It works… most of the time only if the original text string does not have space before/after the line breaks.  It is difficult to tell if there is a space before/after line break, isn’t it????

No worry.  Simply wrap the formula with TRIM( ),


TRIM simply removes all extra space(s) in the text string.

This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

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

You are commenting using your 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