U Microsoft Excel, professionals are faced with various problems and situations. It may be possible to work with a block of text that is located in the same area of the table. It’s a pity that so many koristuvachs hear songs of difficulty like this. In this article, we will figure out how to transfer text to the market in Excel. Let's get started. Let's go!
Apparently, if you are in one middle, pressing the Enter key will move the cursor to the next row of the table, which is unpleasant for our situation. This kind of task comes up often and is simpler than most people think. To move text in one middle, use the Alt+Enter key combination. This method is good for situations where you need to cancel an operation once or a few times.
You can also use this additional Excel function. This useful function allows you to completely simplify the configuration of most tasks in the program. For example, enter your contact details (telephone, email, home address) in each box so that the smells are automatically collected in one box.
I’ll first look at the text areas (“phone:” + “12345678”; “email:” + “ [email protected]"; “Addresses:” + “vul.A, budynok. 27-G"). It is necessary to add the sign “&” (ampersand) between the addresses of the middle ones.
Now it is necessary to put together a formula, because the mother is guilty of this look: “=B1&C1&SYMBOL(10)&B2&C2&SYMBOL(10)&B3&C3”.
SYMBOL (10) - this is the transfer of the row, which is obtained after skin pairing of the connected areas.
If the text is not displayed correctly when transferred, right-click on required areas and in the menu select the item “Middle Format”. In the window, go to the “Verification” tab. In the “Vision” section, mark the “transfer with words” item with a bird.
If you need to move the words written in the column into the box so that they stand in one row across the space, write the next formula in a special field for the function: “=PENDANT(B2);CHARACTER(10);” "".
To cancel the operation at the gateway, replace the formula with the words “SYMBOL (10)” and a space (" ").
There is a method that allows you not to go into the complexity of formulas. Right now you need to see the text and press the key combination Ctrl+H. In the window that appears, in the “Know” field, enter 010 and the Alt key. Please note that the row transfer code you enter will not be displayed. Next, in the “Replace with” area, enter the character with which you would like to replace the row transfer sign; You can find all the special signs and symbols that are used every day on the keyboard in a special Excel table.
For those who use VBA ( Visual Basic) in Excel, the approach will also be relevant. Once you have identified the areas you need, select the Macros window. Then, depending on what you need to do, write one or other commands, as shown in the screenshot below:
For ease of reference, copy the command below:
1. Changing the gaps on transfers in the seen middles with the help of VBS:
Sub ProbesTransfer()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(32), Chr(10))
2. You can change the transfer to the clearings on the visible middles with the help of VBS:
Sub TransferToProblems()
For Each cell In Selection
cell.Value = Replace(cell.Value, Chr(10), Chr(32))
De Chr(10) and Chr(32) - codes for the transferred row and the pass is valid.
The skin from these methods is kind and gentle in its own way. Choose the one that is best suited for the top production line. How are you talking? similar problem It looks like it's easy to finish. Use the functions of Microsoft Excel to automate the execution of certain operations in the program to save time and improve productivity and efficiency. Write in the comments if this article helped you with your food supply and continue to learn more about the great capabilities of the Microsoft Excel spreadsheet editor.
In Excel, there are a number of options for moving text in the middle of one middle.
1 way
You will need to quickly use the formatting tool for your business.
1) Right-click on the desired location or on several spaces in which you need to transfer text. From the context menu, select “Comic Format”.
2) The formatting window opens. You need to open the “Verification” tab and check the “Wrap after words” box in the “Verification” box.
3) I forgot to press the “OK” button. The text will be transferred and displayed not in one row, but in several.
Method 2
1) See the desired commercial, to do this just click on it with the left mouse button.
2) On the Excel toolbar, click the “Wrap text” button.
This method is very simple, low-tech, and takes less than an hour.
3 way
1) Go to text editing mode by clicking the left mouse button in the middle of the box. You need to place your cursor in front of the part of the text that needs to be moved.
2) Now type the key combination “Alt” + “Enter” on the keyboard. The text will be divided.
3) To improve the residual result, it is enough to enter the editing mode in the middle.
To exit the editing mode, you can press the "Enter" key or press the left mouse button once on any other part of the table.
Standard rules for typing text in Excel windows do not allow you to type text in multiple rows. I primarily press the Enter key, especially for Word programs, Bring it to the next stage of the trade.
The placement of text in rows can be specified both after typing and during typing. In the first case, the text in the middle consists of one paragraph, and in the other - several paragraphs.
How to insert multiple rows of text without creating paragraphs?
In this case, the text in the box consists of one paragraph, and the number of rows is equal to the width of the box.
1 way:
- In the “Verification” group, click the “Move text” button (Fig. 3.19).
Method 2:
- At the window of the open arch, see the required middles (with or without text) and go to the “Head” tab.
- In the “Verification” group, click the “Component Format” button (or use the Ctrl+Shift+F keys).
- In the “Format of Middle Words” window, on the “Verification” tab, in the “Verification” group, activate the “Wrap after words” item.
- Close the window with the "OK" button.
Porada
Next, when adjusting the width of the column, check the required number of rows for the text in the store.
How to create multiple rows of text to create paragraphs?
In another version, the row in the box will be a closed paragraph, and the number of rows will be the same regardless of the width of the column.
- At the window of the open arch, type the first row of text from the required merchant.
- At the end of a row, to move to another row of text, press the keys Alt+Enter.
- Next, for consumption, use the received keys to create offensive rows from the merchant.
Most of the adjustments in Microsoft Excel are based on the combinations, between them: one middle - one row. However, there may be no fallouts, if in the middle it is necessary to create a few rows. For this there are several ways you can choose.
In addition to placing the words in one middle, there is often a problem with writing a long word: either its part is transferred to another row (if you press Enter), or it gets stuck on the middle column.
The office product Excel offers a wide variety of solutions. Transferring the row to Excel middle No blame: from manual ripping to automatic transfer from a given formula and programming.
Vikorist's key combination
Whose version has the same key “ Alt+Enter" It's best to press "Alt" right away, then don't release the keys. Enter».
You can transfer text into commercials using this combination at the time of typing or after all words are written in one row. You need to place the cursor in front of the required word and press the corresponding key to create a new row from the merchant. 
If you need to write a great text, you can use the command “ middles format“What can you do to put the text in one middle Excel.
Pressed by the bear see I need the area. Right button Click on the dialog box with commands. Vibrati " middles format». 
On the top panel you can see the section “ virivnyubnya». 
Check the box next to the command " carry beyond words(column “image”). Click the OK button. 
After this dialogue window will close. Now the middle will fill up long text, will be transferred from work automatically. If the word does not fit in width, then Excel program move the row from the merchant yourself. 
If there are a number of such averages, you need to see them using the left mouse button and create instructions for a better algorithm of action. All vision areas will appear automatically transferred.
Formula wiki
If you need to not only divide the text into rows, but then collect information from many places, then overdone methods are not suitable. In this case, a number of types of formulas are transferred behind the words:
- symbol ();
- zchepiti();
- substitute().
Symbol
In the middle of the arms enter the code– digital values from 1 to 255. The code is taken from a special table, where the number and type of transfer symbol are indicated. The transfer code is 10. So, the formula is “symbol (10)”.
Rozberomo on specific application, how to work with the formula “symbol (10)” For the cob, remember the middles, which are common throughout the year. 4 columns of the first row – nickname, become, century, light. Friend - Ivanova, druzhin, 30, vyshcha. 
Select, then the middle is visible Where can we move from here? Place the cursor on a row of formulas. 
Let's remember the formula(For the middle one):
A1&A2&Character(10)&B1&B2&character(10)&C1&C2&character(10)&D1&D2
The sign “&” means the merging of the corresponding middles, and the symbol (10) means the transfer of the row after the sep- arate pair. 
After writing the formula, press the “ Enter" The vision of the area will show the result. 
The screenshot shows that the format is not tasks. Vikoristovuyemo " row format“, as it was designated more. After the checkbox next to the item “ carry beyond words“I will see the middle as in the picture below. 
Another way to quickly defeat your team. The right hand is in the fire section " Format" Click on the small black arrow to open the dialog box. There is a team downstairs who are looking for something. 
If you replace the output data with something else, it will also change in the middle.
Zcepiti
The function “zchepit()” is similar to the one before. It is also specified by a formula, except at the arms it is not the code that is indicated, but the formula “symbol (10)”.
For example, take the first row of 4 columns.
Apparently the area to be moved is directed by the cursor to a row of formulas. Let's write it down:
ZCHEPITI(A1,CHAR(10),B1,CHAR(10),C1,CHAR(10),D1)
Press the key " Enter». 
As for " carry beyond words" We reject: 
Number of middles for zcheplenya you can buti-yake. 
The main advantage of this method is that changing these rows and columns does not change the formula, but rather the algorithm itself.
Substitute
If the middle contains a lot of words and you need to move them to another place at the same time as they were transferred, then use the formula “substitute()”.
We enter the required text up to A4. 
Then with the left mouse we see A6, we write in the term of formulas:
PIDSTAVITY(A4;" ";CHAR(10))
The formula is inserted into the address in the middle of the text - A4. After pressing the button " Enter"We reject the result. 
It is important not to forget to check the box opposite the command for the visible area. carry beyond words».
Replacing the sign moved to a space and back
Sometimes you need to replace the carry sign with a space and create a text stopper with the same text. There are many ways to do this. Let's look at two of them:
- vikno " know and replace»;
- VBA scripty.
Look for the replacement to appear on the same keys Ctrl+H. For ease of use, you first need to press the Ctrl key, then press the English letter H. You will see a dialog box with the parameters that are being configured. 
At the field " know» enter trace Ctrl+J(I'll press the key first, then type the letter). With this field, you will be left practically empty (there will be no more visible point). 
At the field " replace with“You need to put a clearing (or a splint), as well as some other sign to which you plan to change the transfer.
The program shows the area of the file with the required values. After what to lose to press " replace all». 
The stacks of words will be re-arranged into rows with spaces. 
VBA script
You can open the editor window using the following keys Alt+F11.
The editor has a panel " VBAProject» Click on the required file. Click the right button context not menu. Let's choose from the beginning " Insert", then " Module». 
A window will appear to insert the code. 
We'll print the code there. What needs to be replaced transfer pass, then we write:
Sub ReplaceProbes() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(32), Chr(10)) Next End Sub
Yakshcho just like that:
Sub ReplaceTransfer() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(10), Chr(32)) Next End Sub
De Chr (10) is the code for the transferred row, and Chr (32) is the skip code. 
Visible window, where you need to press the button " НІ». 
Further is necessary save the document for more help with a new macro. 
To close the editor, press “ Alt+Q».
The main disadvantage of this method is the need for basic knowledge of VBA.
Various solutions to problems allow Microsoft Excel users to choose the appropriate method for themselves.
It is often necessary to transfer the text to a new row in the middle of one Excel work. To move the text in rows in the middle of one middle, as indicated in the picture. If, after entering the first part of the text, simply press the ENTER key, the cursor will be moved to the leading row, or the other middle, and we need to be moved to that middle.
This is very often done and it is even simpler - to move the text to a new row in the middle of one Excel you need to press ALT+ENTER(Hold down the ALT key, then without releasing it, press the ENTER key)
How to move text to a new row using Excel using an additional formula
Sometimes you need to transfer rows more than once, but using an additional function in Excel. The axis is like this butt of the little one. We enter the name, nickname and father's name and it automatically goes to the middle A6
In the window that has opened, in the “Verification” tab, you must check the box opposite “Transfer after words” as indicated in the picture, otherwise the transfer of rows in Excel will not be correctly displayed using additional formulas.
How to replace the carry sign with another symbol and back in Excel using an additional formula
possible change the transfer symbol to any other sign, for example, for a break, for help text functions Enter in Excel
Let's take a look at the butt to see what's better in the picture. So, in the middle of B1 we write the function ADD ADD:
Code:
PIDSTAVITY(A1,CHAR(10);" ")
A1 - this is our text from the moved row;
SYMBOL (10) - this row has been moved (we looked at many more things in this statti);
" " - this is not a pass, so we can change the transfer of the row to the pass
What you need to earn turnaround operation- replace the space with the transfer sign (symbol), then the function looks like this:
Code:
PIDSTAVITY(A1;" ";CHAR(10))
I would like to remind you that in order for the transfer of rows to be displayed correctly, you need to select “Transfer in rows” in the “Verification” section.
How to change the carry sign to a space and back in Excel for additional help SEARCH - REPLACE
Vypadki are eliminated if the formulas are not easy to use and it is necessary to replace the shortcut. For this reason, it is quick to search and replace. We see our text and press CTRL+H, it will appear now.
If we need to change the transferred row to a space, then in the “Know” row it is necessary to enter the transferred row, for which stand in the “Know” field, then press the ALT key, without releasing it, type 010 on the keyboard - this is the transfer code row, no will be visible in this field.
After this, enter a space or any other character in the “Replace with” field and click “Replace” or “Replace all”.
Before speaking, Word has not implemented this completely.
If you need to change the symbol for moving a row to a space, then in the “Know” field you need to enter a special code “Row moving”, which is indicated as ^l
In the “Replace with:” field, you just need to create a space and click on “Replace” or “Replace All”.
You can change not only the transfer row, but also other special symbols, to remove the code you need, you need to click on the “More >>”, “Special” button and select the code you need. I'll guess what function given Only in Word, these symbols cannot be processed in Excel.