Contents
Line wrapping is a great way to improve the appearance of long text in a cell. This allows you to fit more information inside it without increasing the width of the column.
The standard method for adding line breaks to a cell is to use the Alt + Enter key combination. It has become widespread. All this looks beautiful, but to a certain extent impractical. After all, these tables did not work yet, and here additional difficulties arise.
In some cases, such a hyphenation is added automatically when the text is copied from other programs. And this can already become a problem. However, if you know a few tricks, you can make your life much easier.
Symbol function
If you need to enter a large amount of data regularly, it is much better to automate the addition of line breaks than to press the above key combination every second.
For this you can use the function SYMBOL, which allows you to insert any ASCII character into a cell. But before describing how to apply it, you need to understand how it works.
Excel constantly uses different character codes. Each letter, pictogram, has its own code from 1 to 255. For example, the code for the capital letter A is 97. The line break has the number 10. And although this character is invisible, it helps the program understand where to wrap. Therefore, it is very important.
Before wrapping a line, you must enable the “Text Wrapping” mode. This way you can make sure the line break character is not visible.
If hyphenation is added to a histogram, graph, or chart, line breaks naturally occur. That is, with its help, you can split one line into two or more, not even in cells, but in absolutely any other Excel object.
Removing line breaks
As mentioned above, if you add line breaks, these invisible characters can cause a lot of inconvenience. But with the right approach, you can figure out how to get rid of them.
Since you already know that adding line breaks is possible with the Ctrl + Enter combination, you still have to figure out how to eliminate them if there are additional characters. After all, no one will deny that it is impossible to start without the ability to slow down. Therefore, being able to remove line breaks is very important.
Of course, you can try to turn this operation manually, deleting the lines one by one. But if there are too many, then it will take a lot of time and require a lot of effort.
So what can be done to automate the process of removing line breaks? This can be done in several ways.
Replacing a hyphen
The easiest way to remove a hyphen is to use Excel’s standard Find and Replace feature. You need to select all the cells available in the document by pressing the key combination Ctrl + A and calling the corresponding dialog box using the key combination Ctrl + H. You can also open the Find and Replace window from the Home tab on the Ribbon, where you find and select the Find group and click the Replace button.
Here, an attentive and thoughtful reader will have a question: what to enter in the upper “Search” field if the symbol is invisible and it is not on the keyboard? And it’s not possible to type the “Alt+Enter” key combination here, so you can’t copy it from a finished spreadsheet.
But this combination has an alternative – Ctrl + J. This keyboard shortcut can replace Alt + Enter in any dialog box or input field.
After the user adds a blinking cursor to the “Find” field and enters the key combination Ctrl + J, nothing visually changes. This should not be embarrassing, since the character itself is invisible, and with all the desire of the Excel user, it cannot be detected.
In the “Replace” field, you can not write anything. If you don’t want the lines to be connected, you can put a space. As a result, there will be free space between the lines. To confirm the action, you need to click the “Replace All” button. Everything, after this transfer will not be in the entire document.
Fair and backward. If you enter a space in the “Find” field and press Ctrl + J in the “Replace” field, then all spaces will be replaced with line breaks. This can be very handy in some situations. For example, if there is a list of words that were randomly printed in a row, while they should be put in a column. In this case, you can open the same Find and Replace window and replace the space with a line break character.
It is important to take into account one more point. The newline character entered in the Find field may remain after the replacement when this dialog box is opened a second time. Therefore, it is better to call it again and press the “Backspace” button several times, while the cursor will be placed in the field.
Formula for removing line breaks
If you need to use a formula to only remove line breaks, you should use the function PRINT, designed to remove all invisible characters. Line breaks are no exception.
But this option is not always suitable, because after removing the line breaks, they can stick together, so it’s better to use another formula that will replace the line break with a space.
For this, a combination of functions is used SYMBOL и SUBSTITUTE. The first returns a character by code, and the second replaces the character with other text. In most cases, line breaks should be replaced with spaces, but you can also replace them with commas.
Division into columns by line break
The Text by Columns tool, located on the Data tab, also works well with line breaks. To do this, in the second step, select the “Other” separator and enter the newline character using the key combination Ctrl + J.
If the table has multiple line breaks one after the other, the Treat consecutive delimiters as one checkbox. And after you click next button, we will get this result.
Attention! Before starting this operation, you need to insert as many columns with no values as possible into the cells to the right of the common column so that the resulting text does not exclude the contents of the cells located to the right of those in which the line break works.
How to split text into multiple lines?
Excel gives you the ability to divide text that consists of multiple lines into separate lines.
It is rather problematic to achieve this goal on your own. You can use a macro to automate a task. To do this, however, you must learn programming skills. Not everyone needs this, since the use of macros is necessary only for those who devote a lot of time to spreadsheets and have to spend a lot of time on them. But the average user does not work with only one Excel, he has many other programs.
But how to do without programming skills, if you still have to constantly separate lines? The easiest way is to use Power Query.
Description of the Power Query add-in
By default, this add-in was first included as standard with Excel 2016. However, if you are using an older version, you can download it from the Microsoft website. Power Query is an addon that allows you to connect to different data and process it for further analysis. It can also be used to split lines.
To load data into Power Query, you must convert it to Smart Table format. To do this, press the key combination Ctrl + T or click the “Format” button, as in the table on the “Home” tab. But if you do not want to do this, there is a way to do otherwise.
If you want to work with “stupid” tables, you can simply select the original entry and give it a name in the “Formulas – Name Manager – New” tab.
Next, on the “Data” tab (in Excel starting from 2016 version) or on the Power Query tab (if Excel is an older version with the add-on installed), you should click on the “From Table/Range” button to transfer the data to the Power Query editor.
After the data is loaded, the column with multiline text is highlighted, and on the “Home” tab, find the option “Split column – By separator”.
Most likely, Power Query will independently determine which criteria the split should be based on, and will itself place the # (lf) line break character so that the user can see where this invisible character is. If necessary, you can use other characters selected from the drop-down list located at the bottom of the window after selecting the “Split using special characters” checkbox.
In order for the data to be split into rows rather than individual columns, you must enable the appropriate selector in the advanced options group.
It remains only to confirm the settings by pressing the OK button and get the desired result.
The resulting table can be inserted into the worksheet using the command “Close and load” – “Close and load into …”. You can find it on the Home tab.
Note that Power Query requires data to be refreshed after each change because there is no input for formulas. To do this, right-click on the table and select Refresh from the drop-down menu. Or you can find the Refresh button on the Data tab.
Using a Macro to Divide a String
Macros are a handy method for automating any workflow in Excel. Although it opens up great possibilities for those who use it, this tool requires a very intensive training because it involves programming.
To use a macro to split lines with hyphens, you must go to the “Developer” tab or press Alt + F11. Then a window will open in which the new module is inserted through the “Insert – Module” menu. You then need to copy this code and paste it into the Visual Basic development environment.
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)) ‘divide the text by transfers into an array
n = UBound(ar) ‘determine the number of fragments
cell.Offset(1, 0).Resize(n, 1).EntireRow.Insert ‘insert blank lines below
cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) ‘put data into them from the array
Set cell = cell.Offset(n + 1, 0) ‘move to the next cell
Next i
End Sub
How to remove line breaks using a macro?
If line breaks were added by mistake or appeared there after copying information from another source, then you can use a macro to remove them.
The advantage of this method of removing line breaks is that it only needs to be created once, and then they can be used with any workbook.
The disadvantage is that, as in the previous example, programming skills are required.
Simply paste the following code into the Visual Basic editor.
Sub RemoveCarriageReturns()
Dim MyRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each MyRange In ActiveSheet.UsedRange
If 0 < InStr(MyRange, Chr(10)) Then
MyRange = Replace(MyRange, Chr(10), «»)
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Conclusions
Row division allows you to make working with Excel spreadsheets more convenient. And using the above techniques, you can do it at a professional level.
You also learned how to remove line breaks, if necessary.
We see that there are many different methods for adding and removing line breaks. At first glance, it even seems incredible that all these functions are needed, and there is no surplus. But here everything rests on how often spreadsheets are used.
Thank God, the functionality of Excel is so wide that you can use it to implement absolutely any idea that comes to mind.