Awọn akoonu
Line breaks within the same cell, added using a keyboard shortcut alt+Tẹ 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 Konturolu+H Tabi nipasẹ Ile – Wa ko si Yan – Rọpo (Ile - Wa&Yan - Rọpo). One inconsistency – it’s not very clear how to enter in the top field Lati wa (Find what) our invisible line break character. alt+Tẹ here, unfortunately, it no longer works, copying this symbol directly from the cell and pasting it here also fails.
A combination will help Konturolu+J – that is the alternative alt+Tẹ in Excel dialog boxes or input fields:
Please note that after you put the blinking cursor in the top field and press Konturolu+J – nothing will appear in the field itself. Don’t be scared – this is normal, the symbol is invisible 🙂
To the bottom field Aropo (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 (Rọpo Gbogbo rẹ) and our hyphens will disappear:
iboji: after performing the replacement entered with Konturolu+J invisible character remains in the field Lati wa 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 pa и Backspace.
Removing line breaks with a formula
If you need to solve the problem with formulas, then you can use the built-in function TẸ (MỌ), 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+Tẹ 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 AKỌRỌ (APAPO) searches for our hyphens in the source data and replaces them with any other text, for example, with a space.
Pipin sinu awọn ọwọn nipasẹ fifọ laini
Familiar to many and very handy tool Ọrọ nipasẹ awọn ọwọn lati taabu data (Data - Ọrọ si Awọn ọwọn) can also work great with line breaks and split the text from one cell into several, breaking it by alt+Tẹ. To do this, at the second step of the wizard, you need to select a variant of the custom delimiter character miiran (Aṣa) and use the keyboard shortcut we already know Konturolu+J bi yiyan alt+Tẹ:
If your data may contain several line breaks in a row, then you can “collapse” them by turning on the checkbox Toju itẹlera delimiters bi ọkan (Toju awọn apinpin itẹlera bi ọkan).
Lẹhin ti tite lori Itele (Itele) 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.
Pin si awọn laini nipasẹ Alt + Tẹ nipasẹ Ibeere Agbara
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 Konturolu+T tabi nipasẹ bọtini Kika bi tabili taabu Home (Ile - Ọna kika bi Tabili). 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 Awọn agbekalẹ - Oluṣakoso Orukọ - Titun (Formulas — Name Manager — New).
Lẹhin iyẹn, lori taabu data (ti o ba ni Excel 2016 tabi nigbamii) tabi lori taabu Ibeere Agbara (if you have Excel 2010-2013) you can click on the button Lati tabili / ibiti (Lati Tabili/Ibiti)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 Pipin Ọwọn - Nipa 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 ila (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 Pade ati fifuye – Sunmọ ati gbe sinu… taabu Home (Ile - Sunmọ&Fifuye - Sunmọ&Kojọpọ si…).
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 Imudojuiwọn & Fipamọ (Tọtun) tabi tẹ bọtini naa Mu Gbogbo rẹ ṣiṣẹ taabu data (Data - Tun gbogbo rẹ sọ).
Makiro fun pipin si awọn ila nipasẹ Alt + Tẹ
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 (Olùgbéejáde) tabi awọn ọna abuja keyboard alt+F11. In the window that appears, insert a new module through the menu Fi sii - Module ati daakọ koodu atẹle nibẹ:
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 Makiro taabu developer (Olùgbéejáde - Macros) tabi ọna abuja keyboard 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