I'm sure you know you can enter a new line/line break by pressing Alt+Enter. But working with these newline characters can sometimes be a bit difficult, for example if you want to remove or replace them.
If you have cells with multiple empty lines it can be difficult to quickly replace them all.
Method 1: Do it in Excel
Although a bit awkward and hidden, you can do this in just Excel:
Hold down the Alt key while typing 010 on the numeric keypad in the search box.
The number 10 is the ASCII code for the newline character. This way you can replace the newline character with something else. Make sure that you enter the 010 on the numeric keypad; it does not work if you use the numbers at the top of your keyboard.
If you enter Alt+010 twice in the 'Find what' box and Alt+010 once in the 'Replace with' box, you can replace the blank empty lines within your cells with a single new line character. If the cells contain multiple empty lines after each other you might have to do this search en replace a couple of times after each other until all double newline characters are removed.
Limitation:
You cannot use this method if your cells contains too much text, in that case Excel presents you with the error "Formula is too long".
In Excel you can receive the error "Formula is too long". It will find what you are looking for but you cannot replace the text. This error has nothing to do with formulas; you can also get it if your cells contain only text. In such a case you can use ASAP Utilities to replace the text, because that does not have the limitation.
Method 2: Do it with ASAP Utilities
The benefit of ASAP Utilities is that you see the character you type and the replace is not limited to cells with a certain length
- In the Excel menu choose: ASAP Utilities » Range » Find / Replace in all sheets...
- Find what: {lf}{lf}
Replace with: {lf}
Then press the 'Find all' button and then the 'Replace all' button. If the cells contain multiple empty lines after each other you might have to press the replace button a couple of times until all double newline characters are removed.
If you need to remove certain characters of replace them with another character the ASAP Utilities "Advanced character removal/replace..." utility might also be interesting to know about.