In Excel, how do you delete line breaks?

In Excel, how do you delete line breaks globally? It's totally messing up my text-to-column I need to do.

A more Excel way:

As line-break is CHAR(13), you could try something like this (assuming your text is in A1 and you'll put edited text in B1)

B1: =SUBSTITUTE(A1,CHAR(13),"")

or

B1: =SUBSTITUTE(A1,CHAR(13)," ")

Source: answers.microsoft.com

Q: In Excel, how do you delete line breaks globally?

A: No, Mac's Find & Replace doen't work.

I will walk you thru my version of a fix.

There are lots of website discussions, but, there doesn't seem to be any resolution,

Using just the Find & Replace.

Example from:

1. Open your Excel file

2. Highlight all cells

   1. 

   2. Select the top left corner Above Row 1 & Left of Column A

   3. Or Select a cell and do ctrl+a at least twice
3. Auto Format the Column Heights by Double Left Clicking inbetween row 1 & 2 (any row will work)
   1. Also, Make sure to take off the Wrap Text as well; As this can be misleading if it is on.
4. Should change appearance from this:
   1. To this:
5. Create a New column to the right of the column
6. Then Type in this formula =TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10)," - "))
7. Press Enter
   1. You should now see your cell formatted cleanly
   2. Highlight the newly formatted cell
   3. Double Left Click on the Bottom Right Hand of the Cell to format the whole column with the formula.
8. Now Highlight the entire new column with the formula
   1. Right Click and Copy this column
   2. Then Right Click on the original column
      1. Select Paste Special
      2. 
   3. Select Values radio button & Click ctrl
      1. 
   4. Make sure to Delete the created columns with the formula.

That should at least be a simple workaround.

Your end result should allow you to resize your column heights again down to the 16px default column height.

Formula Reference: https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/

Note: The Manual Find and replace in native Excel doesn't work using keyboard shortcuts

Easiest Way to Remove all line breaks.

Copy the target column containing all the line breaks in to blank Word document (Works for rows and entire tables)

In Word ctrl+F (Command+F for Mac) - To Find and Replace

Enter ^p in the find field

"^p" is the character symbol for line paragraph/break

Key Combo SHIFT + 6 Then p

Enter desired character in replace field (Likely one space)

Click replace all.

Done - Copy column from word back into excel.