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
- Select the range you want to find the line break (unless you want to find the whole worksheet)
- CTRL H to open the Find and Replace dialogue box
- 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)
- Replace with: Either a single space or nothing. (depends on your original data)
- Replace All
Using Formula
=SUBSTITUTE(A2,CHAR(10),” “)
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(SUBSTITUTE(A2,CHAR(10),” “))
TRIM simply removes all extra space(s) in the text string.
wmfexcel 