ọdịnaya
Line breaks within the same cell, added using a keyboard shortcut Alt+Tinye 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 ma ọ bụ site na Ụlọ – Chọta wee họrọ – Dochie (Ụlọ - Chọta&Họrọ - Dochie). One inconsistency – it’s not very clear how to enter in the top field Findchọta (Find what) our invisible line break character. Alt+Tinye 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+Tinye 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 Gbanwee (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 (Dochie ha niile) and our hyphens will disappear:
ndò: after performing the replacement entered with Ctrl+J invisible character remains in the field Findchọta 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 Hichapụ и 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 (Ọ dị ọcha), 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+Tinye 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 AKWỤKWỌ (AKWỤKWỌ) searches for our hyphens in the source data and replaces them with any other text, for example, with a space.
Kewaa n'ime ogidi site na nkwụsịtụ ahịrị
Familiar to many and very handy tool Ederede site na kọlụm site na taabụ data (Data - Ederede gaa na kọlụm) can also work great with line breaks and split the text from one cell into several, breaking it by Alt+Tinye. To do this, at the second step of the wizard, you need to select a variant of the custom delimiter character Ndị ọzọ (Omenala) and use the keyboard shortcut we already know Ctrl+J dị ka ihe ọzọ Alt+Tinye:
If your data may contain several line breaks in a row, then you can “collapse” them by turning on the checkbox Na-emeso ndị na-eme ihe n'usoro dị ka otu (Mee ndị na-eme ihe n'usoro dị ka otu).
Mgbe ịpị Osote (Osote) 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.
Kewaa n'ime ahịrị site na Alt + Tinye site na ajụjụ ike
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 ma ọ bụ site na bọtịnụ Hazie dị ka tebụl tab Mbido (Ụlọ - Ụdị dị ka tebụl). 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 Usoro – Onye njikwa aha – Ọhụrụ (Formulas — Name Manager — New).
Mgbe ahụ, na taabụ data (ọ bụrụ na ị nwere Excel 2016 ma ọ bụ mgbe e mesịrị) ma ọ bụ na taabụ Ajụjụ ike (if you have Excel 2010-2013) you can click on the button Site na tebụl / nso (Site na tebụl/oke)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 Kọlụm gbawara - Site na 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 Ahịrị (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 Mechie ma buo - Mechie ma buo na… tab Mbido (Ụlọ - Mechie&Buchie - Mechie&Buchie na…).
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 Melite & Chekwa (Tinye ume) ma ọ bụ pịa bọtịnụ Melite niile tab data (Data - Weghachite Niile).
Macro maka nkewa n'ime ahịrị site na 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 Mmepụta (Onye Mmepụta) ma ọ bụ ụzọ mkpirisi ahụigodo Alt+F11. In the window that appears, insert a new module through the menu Fanye – Modul ma detuo koodu a ebe ahụ:
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 Mmepụta (Onye Mmepụta - Macros) ma ọ bụ ụzọ mkpirisi ahụigodo 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