In the below image you can see cell value with line break in A column and their respective value in B column without line break.
In this Excel tip I will show you 3 ways to remove or replace line breaks in Excel.
Remove line breaks in using find and replaces
1st select the range from which you want to remove line breaks.
2nd press CTRL+H to bring Find and Replace dialog box.
3rd place cursor in Find What and press CTRL+J to bring invisible line break character (one may get a little dot also) .In Replace with enter one space. Make sure Match Entire Cell Content is unticked.
Click on Replace All.
Remove line breaks using Substitute function
Cell B2 formula =SUBSTITUTE(A2,CHAR(10),” “)
1st input is text or cell reference whose content have line break.
In the above formula 2nd input Char(10) represents or output line break character.
3rd input is single space ” ” which means at place of line break or Char(10) I want to put single space.
The above formula will remove all the instances of line breaks from a text.
If you are getting extra spaces after applying the above formula then wrap the above formula in Trim function to remove extra space.
=TRIM(SUBSTITUTE(A2,CHAR(10),” “))
Note:- You can also use =CLEAN(A2) formula but it will give output without space line1line2line3.
Remove line breaks manually
To remove line breaks manually you just have to place cursor at the line end then press Delete key. To separate line you can put space using spacebar.
You have to repeat above process for each line break till all line breaks of a cell get deleted then just press Enter key.
You can also watch the below video for the same.
https://youtu.be/BbXJuc7ZAi0