It is often required inside one Excel cell to wrap text on newline. That is, move the text line by line within one cell as shown in the picture. If, after entering the first part of the text, simply press the ENTER key, the cursor will be moved to next line, but a different cell, and we need a wrap in the same cell.
This is very frequent task and it is solved very simply - to wrap the text to a new line inside one Excel cell, you must press ALT+ENTER(hold down ALT key, then without releasing it, press the ENTER key)
How to move text to a new line in Excel using a formula
Sometimes you need to do a line break not once, but using functions in Excel. Like in this example in the picture. We enter the first name, last name and patronymic and it is automatically collected in cell A6
In the window that opens, in the "Alignment" tab, you must check the box next to "Wrap by words" as shown in the picture, otherwise line wrapping in Excel will not be displayed correctly using formulas.
How to replace the hyphen with another character in Excel and vice versa using a formula
Can change the hyphen character to any other character, for example to a space, using text function SUBSTITUTE in Excel
Consider the example in the picture above. So, in cell B1 we write the SUBSTITUTE function:
SUBSTITUTE(A1,CHAR(10), " ")
A1 is our line break text;
CHAR (10) is a line break (we discussed this a little higher in this article);
" " is a space, since we are changing the line break to a space
If you need to do reverse operation- change the space to a hyphen (symbol), the function will look accordingly:
SUBSTITUTE(A1, " ", CHAR(10))
I remind you that in order for line wrapping to be reflected correctly, it is necessary to specify “Wrap across lines” in the properties of the cells, in the “Alignment” section.
How to change hyphen to space and back in Excel using SEARCH - REPLACE
There are times when formulas are inconvenient to use and you need to make a replacement quickly. To do this, we use Find and Replace. Select our text and press CTRL + H, the following window will appear.
If we need to change the line break to a space, then in the "Find" line you need to enter a line break, for this stand in the "Find" field, then press the ALT key, without releasing it, type 010 on the keyboard - this is the line break code, it will not be visible in this field.
After that, in the "Replace with" field, enter a space or any other character you need to change to and click "Replace" or "Replace All".
By the way, in Word this is implemented more clearly.
If you need to change the line break character to a space, then in the "Find" field you need to specify special code"Line break", which is denoted as ^l
In the "Replace with:" field, you just need to make a space and click on "Replace" or "Replace All".
You can change not only line breaks, but also others Special symbols to get their corresponding code, you must click on the button "More >>", "Special" and select the code you need. I remind you that given function is only in Word, in Excel these characters will not work.
How to change line break to space or vice versa in Excel using VBA
Consider an example for selected cells. That is, we select the required cells and run the macro
1. Change spaces to hyphens in selected cells using VBA
Sub SpacesOn Hyphenation()
For Each Cell In Selection
cell.Value = Replace(cell.Value, Chr(32) , Chr(10) )
Next
end sub
2. Change hyphens to spaces in selected cells using VBA
Sub Carry onSpaces()
For Each Cell In Selection
cell.Value = Replace(cell.Value, Chr(10) , Chr(32) )
Next
end sub
The code is very simple Chr (10) is a line break, Chr (32) is a space. If you want to change to any other character, then simply replace the code number corresponding to the required character.
Character codes for Excel
The picture below shows various symbols and their corresponding codes, several columns are a different font. To enlarge the image, click on the picture.
After the table is created, you may find that some rows need to be moved to a different partition or even to a different table for reordering purposes. The Smartsheet app allows a sheet owner and collaborators with editor or admin rights and . To perform this operation, collaborators must have the same level of access to the table into which the rows are being moved.
Moving a row within a table
- Click a row number and drag it up or down in the table. A dark one will appear horizontal line, indicating where the line will be moved when you release the mouse button.
- Release the mouse button to move the line.
ADVICE. To move a row within a table, you can also cut and paste it. Click the line number and press CTRL+X (Windows) or CMD+X (Mac) to copy the line. Then click the line number you want to move to and press CTRL+V (Windows) or CMD+V (Mac) to paste the line. - You can move multiple rows at the same time by selecting them first.
- If the rows are consecutive, click the first row number, scroll the table to the last row, press and hold the SHIFT key, and then click the last row number to select all the rows in between. Drag the number of one of the selected rows to move them all to a new location.
- If the lines are scattered, hold down CTRL (on Windows) or CMD (on Mac) and click the numbers of the lines you want to highlight them. Drag the number of one of the rows to move them all to a new location.
NOTE. If a filter is applied to a table, the rows may not be in order.
Move row to another table
If you want to keep the data in the original table, we recommend using the .As in any activity, not always when working with Excel file it is possible to build a table the first time and enter the data in such a way that it does not require changes in the future. In particular, there are situations when it is necessary to change the location of elements: columns or rows.
Moving a column in a table
There are several ways to swap both columns and rows in Excel. Let's stop on moving columns. Highlight the column you want to move to another location by clicking on the heading. Move the mouse cursor to the edge of the selection so that it turns into a crossed double-headed arrow icon.
Now, if you have a version of Excel 2007, just drag the column to a new location. If the version of your program is 2010 and higher, dragging must be done with the Shift button pressed. This way you can move one or more columns. If you need to move adjacent columns, simply select them with the mouse. To select nonadjacent columns, use Ctrl button.
Another way to change serial number graphs - copy and paste. Before swapping columns in Excel, decide on the column where you want to put them. Select the column in front of which the inserts will be placed and insert an empty column. This can be done using the "Insert" context menu or the "Insert" item of the "Cells" toolbar on the "Home" tab. You need an "Insert Columns to Sheet" element.
Next, select the desired column and copy it. To do this, use the "Copy" button on the "Clipboard" toolbar of the "Home" tab. Another method is to use context menu right mouse click "Copy". And finally, just pressing the Ctrl + C combination.
Then select the top cell of the new column and paste the copied data with right button mouse, the "Paste" operation on the clipboard panel or the Ctrl + V combination.
After we swapped the columns in Excel, our table got two identical columns. The old one can be removed by selecting it and choosing the “Delete” item from the right mouse button menu or by using the “Remove columns from sheet” item in the “Cells” panel.
To avoid deleting a column instead of copying it, apply cuts. With the column selected, use the Cut command from the toolbar or mouse context menu. Then mark the column before which you want to add data, and perform the "Insert Cut Cells" operation.
Many people are concerned about whether data, formulas, and calculations are preserved after the columns are swapped. In Excel, copy-pasting formulas in cells continue to refer to the fields in the old range. Accordingly, when deleting the old column, an error will occur.
When inserting a cut column, this procedure is painless. All information contained in the moved columns is preserved, and cell references are redirected to the new data location.
Move rows
Often the user needs to change the order of the records in the table: raise some higher, lower others. It is not always possible to cope with such a task using sorting. And then you have to move the lines manually.
You can move a row or series of rows just like swapping columns in Excel. That is, by separating required records and dragging them while holding Shift key in Right place(or without Shift in Excel 2007) or cut and paste.
Swap columns and rows
But sometimes the task is more difficult - to make the titles of the records become the titles of the columns and vice versa, in other words, to swap columns and rows. Excel has the ability to do this.
To do this, copy the entire table, select a new location where its modified (transposed) version will be located, and click to paste right key mice. Select the "Transpose" paste option and click the button. There is a similar command in the "Paste" menu of the clipboard panel.
With the help of the above information, you can easily change the arrangement of records and columns in Excel in the future.
Most settings Microsoft Excel are set by default, among them the restriction: one cell - one line. But there are many cases when you need to make several lines in a cell. To do this, you can use several methods.
In addition to the arrangement of words in one cell, it often turns out to be a problem to write long word: either part of it wraps to another row (when using Enter), or it runs into an adjacent column.
Office product Excel for many tasks has a variable line of solutions. Line wrap in cell in Excel no exception: from manual rupture to automatic transfer from given formula and programming.
Using a keyboard shortcut
In this case, use the keyboard shortcut " Alt+Enter". It will be more convenient for the user to first hold down “Alt”, then, without releasing the keys, press “ Enter».
You can wrap text in a cell with this combination at the time of typing or after all the words are written in one line. Need before the right word put the cursor and press the specified key combination - this will make a new line in the cell. 
When you need to write a large text, you can use the command " cell format”, which will fit the text in one Excel cell.
By mouse click highlight required area. Right-click to open a dialog box with commands. Select " cell format». 
On the top panel highlight the section alignment». 
Put a tick against the command " transpose by words” (column “display”). Press the OK button. 
Then the dialog box will close. Now the cell is filled long text, word wrap will be carried out automatically. If the word does not fit in the width, then Excel program itself will transfer the line in the cell. 
If there should be several such cells, you need to select them all with the left mouse button and perform the above algorithm of actions. The entire selected area will automatically wrap.
Using formulas
When the user needs not only to divide the text into lines, but to pre-collect information from several places, then the above methods are not suitable. In this case, several types of word transfer formulas are used:
- symbol ();
- concatenate();
- substitute().
Symbol
Inside brackets the code is indicated – digital value from 1 to 255. The code is taken from a special table, where the number and the corresponding hyphen character are indicated. The transfer has a code - 10. Therefore, the formula is used - "symbol (10)".
Let's take a look at specific example how to work with the formula "symbol(10)". First, let's fill in the cells, which we will later merge. 4 columns of the first line - last name, gender, age, education. The second is Ivanova, female, 30, higher. 
Choose then select a cell, where we will transfer. Put the cursor in the formula bar. 
Filling in the formula(for selected cell):
A1&A2&CHAR(10)&B1&B2&CHAR(10)&C1&C2&CHAR(10)&D1&D2
Where the sign "&" means the concatenation of the corresponding cells, and the symbol (10) means a line break after each concatenated pair. 
After writing the formula, press the " Enter". The result will appear in the selected area. 
The screenshot shows that the format is not set. We use " string format", as mentioned above. After the checkbox is ticked in front of the item " transpose by words”, the cell will look like in the picture below. 
There is another way to quickly use this command. At the top right there is a section Format". Click on the little black arrow to bring up a dialog box. Below is the command you are looking for. 
If you replace the original data with others, the contents of the cell will also change.
concatenate
The "hook()" function is similar to the previous one. It is also given by a formula, but not the code is indicated in brackets, but the formula “symbol (10)”.
Take for example only the first row with 4 columns.
Let's select the area for transfer and direct the cursor to the formula bar. Let's write:
CONCATENATE(A1,CHAR(10),B1,CHAR(10),C1,CHAR(10),D1)
Press the key " Enter». 
Let's set " transpose by words". We get: 
Number of cells for clutch can be anything. 
The main advantage of this method is that changing these rows and columns does not change the formula, it will have the same given algorithm.
Substitute
When a cell contains a lot of words and you need to move them to another place immediately with a transfer, then use the “substitute ()” formula.
We bring desired text in A4. 
Then, with the left mouse, select A6, write in the term of the formulas:
SUBSTITUTE(A4;" ";CHAR(10))
In the formula, insert the address of the cell with the text - A4. After pressing the key " Enter» get the result. 
It is important not to forget for the selected area to put a tick in front of the command " transpose by words».
Replacing a hyphen with a space and vice versa
Sometimes you need to replace the hyphen with a space, make a column of words a solid text. There are several ways to do this. Let's consider two of them:
- window " find and replace»;
- VBA scripts.
Find and Replace opens with a keyboard shortcut ctrl+h. For convenience, first you need to hold down Ctrl key, then press English letter H. A dialog box with customizable options will pop up. 
In field " to find» must be entered Ctrl+J(First holding down the key, then typing the letter). In this case, the field will remain almost empty (there will be only a barely noticeable flashing dot). 
In field " replaced by» you need to put a space (or several), as well as any other character to which you plan to change the transfer.
The program will highlight the area of the file with the desired values. Then it remains to press " replace everything». 
Columns of words will be rearranged into lines with spaces. 
Using VBA script
You can open the editor window with a keyboard shortcut Alt+F11.
In the editor we find the panel " VBAProject and click on the desired file. Right-click to call the context menu. Select first " Insert", then " module». 
A window for pasting the code appears. 
We type in the code. If you need to replace gap to carry, then we write:
Sub ReplaceSpaces() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(32), Chr(10)) Next End Sub
If vice versa:
Sub Replace() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(10), Chr(32)) Next End Sub
Where Chr(10) is the line break code and Chr(32) is the space code. 
A window will pop up where you need to click the " NO». 
Next, you need save the document with new macro support. 
To close the editor, click " Alt+Q».
The main disadvantage of this method is that basic knowledge of the VBA language is required.
Variable solutions to the problem allows Microsoft user Excel to choose the method that suits you.
Data located in a cell or a range of cells can be moved from one place in the worksheet to another using both direct drag and drop, buttons on the ribbon tabs, context menu, and procedures (macros)- programs written in language Visual Basic for Application, VBA for short.
Moving with the mouse
The easiest way to move cells from place to place is to use the mouse. To move a cell to a new location, you need to select the desired cell or range, move the mouse cursor over the border of the selected range so that the cursor becomes an inclined arrow with a cross, as shown in the figure and with the left mouse button pressed, drag the selected area to a new location and after that release the mouse button. If, when you hover the mouse cursor over the border of the selected range, it does not turn into a slanted arrow with a cross and makes it impossible to drag cells, then it is disabled. special setting and it must be enabled by clicking on the "Office" button in the upper left corner of the program and going to Excel Options/Advanced. In the Editing Options group, check the box next to Allow fill handles and cell dragging.
Cut and paste
To transfer cells, along with moving them with the mouse, cut and then paste are used. In order to cut/paste a cell or a range of cells, select the "Cut" command, move the cursor to a new location and select the "Paste" command. To do this, you can use the context menu (the menu that appears when you right-click in the area of the selected area), the Clipboard group on the Home tab, or hot keys (a keyboard shortcut to which this or that command is assigned).
The keyboard shortcut for cutting is Ctrl+X or Shift+Delete. The keyboard shortcut for pasting is Ctrl+V or Shift+Insert.
When moving a large number cells, there is a desire to select all the necessary cells at once and move them all at once. Standard Excel tools allow you to do this only with a range. If a group of nonadjacent cells is selected, then Excel will display the message " This command not applicable for unbound ranges. Select one range and select the team again." In this case, you can first move the cells so that they can be selected by one range, and then continue working with them.
In addition, there can be so many cells that transferring them manually can turn into a nightmare. Another problem that may arise when moving cells from place to place may be the search for the cells necessary to move. For clarity, let's consider the following example: it is necessary to spread the cells into different columns, depending on their values.
All these and similar tasks are easily solved with the add-in for Excel.
Add-in for fast moving cells
For fast transfer cells from one place to another, an Excel add-in is created, and the button that launches this add-in is displayed on separate tab Excel ribbons or separate panel tools (depending on Excel versions). Pressing the button brings up a dialog box in which the user can select the conditions, values, ranges, direction of movement, and so on.
Moving is done automatically with a macro. In the example above, the following conditions were applied: find all cells with numerical values containing "3100" and move them 1 column to the left and 1 row up.
