Contents
Line breaks within the same cell, added using a keyboard shortcut Alt+Enter is a very common and common thing. Sometimes they are made by users themselves to add beauty to long text. Sometimes such transfers are added automatically when unloading data from any working programs (hello 1C, SAP, etc.) The problem is that then you have to not only admire such tables, but work with them – and then these invisible characters transfers can be a problem. And they may not become – if you know how to handle them correctly.
Let’s take a look at this issue in more detail.
Removing line breaks by replacing
If we need to get rid of hyphens, then the first thing that usually comes to mind is the classic “find and replace” technique. Select the text and then call the replacement window with a keyboard shortcut Ctrl+H or via Home – Find and Select – Replace (Home — Find&Select — Replace). One inconsistency – it’s not very clear how to enter in the top field To find (Find what) our invisible line break character. Alt+Enter here, unfortunately, it no longer works, copying this symbol directly from the cell and pasting it here also fails.
A combination will help Ctrl+J – that is the alternative Alt+Enter in Excel dialog boxes or input fields:
Please note that after you put the blinking cursor in the top field and press Ctrl+J – nothing will appear in the field itself. Don’t be scared – this is normal, the symbol is invisible 🙂
To the bottom field Substitute (Replace with) either do not enter anything, or enter a space (if we want not only to remove hyphens, but to replace them with a space so that the lines do not stick together into a single whole). Just press the button Replace everything (Replace All) and our hyphens will disappear:
Nuance: after performing the replacement entered with Ctrl+J invisible character remains in the field To find and may interfere in the future – do not forget to delete it by placing the cursor in this field and several times (for reliability) pressing the keys Delete и Backspace.
Removing line breaks with a formula
If you need to solve the problem with formulas, then you can use the built-in function PRINT (CLEAN), which can clear the text of all non-printable characters, including our ill-fated line breaks:
This option, however, is not always convenient, because lines after this operation can be glued together. To prevent this from happening, you need to not only remove the hyphen, but replace it with a space (see the next paragraph).
Replacing line breaks with a formula
And if you want not just to delete, but to replace Alt+Enter on, for example, a space, then another, slightly more complex construction will be required:
To set an invisible hyphen we use the function SYMBOL (CHAR), which outputs a character by its code (10). And then the function SUBSTITUTE (SUBSTITUTE) searches for our hyphens in the source data and replaces them with any other text, for example, with a space.
Division into columns by line break
Familiar to many and very handy tool Text by columns from the tab Data (Data — Text to Columns) can also work great with line breaks and split the text from one cell into several, breaking it by Alt+Enter. To do this, at the second step of the wizard, you need to select a variant of the custom delimiter character Other (Custom) and use the keyboard shortcut we already know Ctrl+J as an alternative Alt+Enter:
If your data may contain several line breaks in a row, then you can “collapse” them by turning on the checkbox Treat consecutive delimiters as one (Treat consecutive delimiters as one).
After clicking on Next (Next) and going through all three steps of the wizard, we get the desired result:
Please note that before performing this operation, it is necessary to insert a sufficient number of empty columns to the right of the split column so that the resulting text does not overwrite the values (prices) that were on the right.
Divide into lines by Alt + Enter through Power Query
Another interesting task is to divide the multiline text from each cell not into columns, but into lines:
It takes a long time to do this manually, it’s difficult with formulas, not everyone can write a macro. But in practice, this problem occurs more often than we would like. The simplest and easiest solution is to use the Power Query add-in for this task, which has been built into Excel since 2016, and for earlier versions 2010-2013 it can be downloaded completely free from the Microsoft website.
To load the source data into Power Query, you must first convert it to a “smart table” with a keyboard shortcut Ctrl+T or by button Format as a table tab Home (Home — Format as Table). If for some reason you do not want or cannot use “smart tables”, then you can work with “stupid” ones. In this case, just select the original range and give it a name on the tab Formulas – Name Manager – New (Formulas — Name Manager — New).
After that, on the tab Data (if you have Excel 2016 or later) or on the tab Power Query (if you have Excel 2010-2013) you can click on the button From table/range (From Table/Range)to load our table into the Power Query editor:
After loading, select the column with multiline text in the cells and select the command on the Main tab Split Column – By Delimiter (Home — Split Column — By delimiter):
Most likely, Power Query will automatically recognize the principle of division and substitute the symbol itself #(lf) invisible line feed character (lf = line feed = line feed) in the separator input field. If necessary, other characters can be selected from the drop-down list at the bottom of the window, if you first check the box Split with special characters (Split by special characters).
So that everything is divided into rows, and not columns – do not forget to switch the selector Rows (By rows) in the advanced options group.
All that remains is to click on OK and get what you want:
The finished table can be unloaded back onto the sheet using the command Close and load – Close and load in… tab Home (Home — Close&Load — Close&Load to…).
It is important to note that when using Power Query, you must remember that when the source data changes, the results are not automatically updated, because. these are not formulas. To update, you must right-click on the final table on the sheet and select the command Update & Save (Refresh) or press the button Update All tab Data (Data — Refresh All).
Macro for division into lines by Alt+Enter
To complete the picture, let’s also mention the solution of the previous problem with the help of a macro. Open the Visual Basic Editor using the button of the same name on the tab Developer (Developer) or keyboard shortcuts Alt+F11. In the window that appears, insert a new module through the menu Insert – Module and copy the following code there:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'determine the number of fragments cell.Offset(1, 0).Resize(n, 1).EntireRow.Insert 'insert empty rows below cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'enter into them data from the array Set cell = cell.Offset(n + 1, 0) 'shift to the next cell Next i End Sub
Return to Excel and select the cells with the multiline text you want to split. Then use the button Macros tab developer (Developer — Macros) or keyboard shortcut Alt+F8to run the created macro, which will do all the work for you:
Voila! Programmers are, in fact, just very lazy people who would rather work hard once and then do nothing 🙂
- Cleaning up text from junk and extra characters
- Replacing text and stripping non-breaking spaces with the SUBSTITUTE function
- How to split sticky text into parts in Excel