Working with tables in Microsoft Excel, users are faced with a variety of problems and situations. It happens that you have to work with a block of text, being in the same area of the table. Unfortunately, for many users this task causes certain difficulties. In this article we will understand how in Excel to make the transfer of text in the cell. Let's get started. Go!
As you know, even being in the same cell, pressing the Enter key, you move the cursor to the next row of the table, which is unacceptable in our case. A similar problem occurs quite often and is solved easier than most users think. To transfer the text in one cell, use the key combination Alt + Enter. This method is good if you need to perform an operation once or several times.
You can do the same with Excel functions. The use of functions can significantly facilitate the implementation of most tasks in the program. For example, enter contact data in separate cells (phone, email, home address) so that they are automatically line by line collected in one cell.
First link the text areas ("phone:" + "12345678"; "email:" + " "; "Address:" + "A street, dom. 27-G"). This is done by adding the “&” (ampersand) between the addresses of the cells.
Now you need to make a formula that should have the following form: "= B1 & C1 & SYMBOL (10) & B2 & C2 & SYMBOL (10) & B3 & C3".
CHARACTER (10) is a line break that is added after each pair of related areas.
In the event that the transferred text is displayed incorrectly, right-click on the desired area and select the “Format cells” item in the menu that appears. In the window that opens, go to the Alignment tab. In the "Display" section, tick the "carry by words" checkbox.
If it is necessary to transfer the words written in a column to a cell so that they stand on one line separated by spaces, write the following formula in a special field for functions: “= SUB (B2); SYMBOL (10);” “”.
To perform the operation in the opposite direction, swap "SYMBOL (10)" and space ("") in the formula.
There is a method that allows not to resort to the use of formulas. First you need to select the text and press the key combination Ctrl + H. In the window that appears, in the Find field, enter 010 while holding down the Alt key. Please note that the line break code you entered will not be displayed. Next, in the "Replace with" area, enter the character to which you would like to replace the newline character, in our case it is a space (""). You can find all sorts of special characters and symbols that are not on the keyboard in the special Excel table.
For those who use VBA (Visual Basic) in Excel, the following method will also be relevant. After marking the required areas, call the macro window. Then, depending on what needs to be done, register one or other commands, as shown in the screenshot below:
For the convenience of copying the command with the same text below:
1. Change spaces for hyphenation in selected cells using VBS:
Sub Spaces for Transfer ()
For Each cell In Selection
cell.Value = Replace (cell.Value, Chr (32), Chr (10))
Next
End sub
2. Change the gaps by gaps to in selected cells using VBS:
Sub Carry On Space ()
For Each cell In Selection
cell.Value = Replace (cell.Value, Chr (10), Chr (32))
Next
End sub
Where Chr (10) and Chr (32) are the line break and space codes, respectively.
Each of the considered methods is in own way good and convenient. Use the one that is more suitable for solving the current problem. As you can see, this problem is solved quite simply. Use Microsoft Excel functions to automate the execution of some operations in the program to save time, improve productivity and work efficiency. Write in the comments if the article helped you to deal with the issues that have arisen and continue to learn something new about the enormous possibilities of the Microsoft Excel spreadsheet editor.