Find And Replace Line Breaks In Excel

It is really easy to insert line breaks into Excel cells, all you need to do is hit ALT+ENTER where ever you want one. I know that I normally use them to make text easier to read in cells, longer pieces of text or bullet points for example.

It is though, slightly harder to remove them if you need to.

Normally if you want to find and replace some text then you would just hit the CTRL+F shortcut to bring up Find and Replace dialog box.

 

 

 

 

 

 

 

 

 

But, if you enter ALT+Enter in Find What, nothing happens apart form your computer beeping at you- not allowed!.

So, instead of the ALT+Enter shortcut we need to enter CTRL+J, this is the shortcut to the line break which is character 10 in the ASCII control code. You can search for line breaks using this shortcut instead.

So if we want to find and place out line breaks with a space.

 

• Select the cells you want to search
• Hit CTRL+H. This will bring up the Find and Replace dialog box with the Replace Tab already active
• In the Find What dialog box hit CTRL+J (it will appear blank- that is ok).
• In the Replace With dialog box just hit the Space bar to enter a space
• Hit Find Next or Replace All to find your cells with to find all of your cells with the line breaks
• Click Replace or Replace All to replace the line breaks with space characters

1. Calculate A Moving Average In Excel

2.Refresh All Your Pivot Tables With One Click

3. Create A Table of Contents