In Excel, how do you delete line breaks globally? It's totally messing up my text-to-column I need to do.
6 Answers
A more Excel way:
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 letter p ) Enter desired character in replace field (Likely one space) Click replace all. Done - Copy column from word back into excel.
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)
or
Source: answers.microsoft.com
nohillside♦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:
- Open your Excel file
Highlight all cells
Select the top left corner Above
Row 1& Left ofColumn A- Or Select a cell and do ctrl+a at least twice
Auto Formatthe Column Heights by Double Left Clicking inbetweenrow 1 & 2(any row will work)- Also, Make sure to take off the Wrap Text as well; As this can be misleading if it is on.
- Should change appearance from this:
- To this:
- Create a New column to the right of the column
- Then Type in this formula
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),'),CHAR(10),' - ')) - Press Enter
- You should now see your cell formatted cleanly
- Highlight the newly formatted cell
- Double Left Click on the Bottom Right Hand of the Cell to format the whole column with the formula.
- Now Highlight the entire new column with the formula
- Right Click and Copy this column
- Then Right Click on the original column
- Select Paste Special
- Select Values radio button & Click ctrl
- 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 Wordctrl+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.
Here's a simple, non-Excel way to get rid of all the line breaks in your text:
Paste the data into TextEdit, put your cursor at the end of any line, then hold shift and press the right arrow once. This will select the invisible new line character. Now hit command + c to copy this invisible character, and then got to
and search for your copied character and replace it everywhere with just a space.
Now you shouldn't have any more line breaks in your text. :)
(On a side note, you don't need to replace with a space you can replace it with another symbol if that may be more convenient for what you're doing in Excel.)
A simple technique which also works in Mac versions of Excel is to use the formula 'clean'. This removes all non-printing characters including line breaks.
grg♦If there are only a few of them, select the cell, then click in the formula box. This makes the formula box act like a text field, so the arrow keys and delete and other editing functions will work. If there are many, use the replace method other posted.
WGroleauWGroleau