home » Goods and services » How to transfer data from word to excel. Pitfalls of copying from Word to Excel. How to get rid of unnecessary characters

How to transfer data from word to excel. Pitfalls of copying from Word to Excel. How to get rid of unnecessary characters

First, you need to understand how the application converts the document. When you import a document into Excel, the program determines what piece of information will be placed in a specific table cell by using certain characters in the text. By formatting the data in the document before importing, you can control the appearance of the resulting table and minimize the need to manually format the document. This method is useful if you need to import a large list from a Word document into an Excel spreadsheet.

This method is especially effective if you are importing a list of several elements of the same format (a list of addresses, telephone numbers, addresses Email, and so on).

Check the document for formatting errors. Before you begin the conversion process, make sure that all list items have the same formatting. Correct if necessary possible mistakes punctuation or rearrange elements that do not match the rest. This will ensure correct data transfer.

Enable formatting marks to be displayed in your Word document. Showing hidden formatting marks will help you choose the best way to separate list items. To display formatting marks, click the "Show all marks" button in the Home tab, or press the key combination Ctrl + ⇧ Shift + *

Most lists will have either one paragraph mark at the end of each line, or one at the end of the line and one on a blank line between items. You'll use these characters to insert the characters that Excel uses to distribute data across table cells.

Remove paragraph marks between elements to eliminate empty lines. Excel uses blank lines between list items to identify individual rows of data, but these now need to be removed for the formatting process to work correctly. Don't worry, you'll add them again a little later. This method is appropriate in cases where one paragraph mark is at the end of the list item and one is in the empty line between the items (two per line).

Enter ^p^p in the search field. This is a code for two paragraph characters per line. If each list item is one continuous line and there are no blank lines between the items, use the ^p command.
In the Replace field, enter a separator character. This mark must not be repeated anywhere else in this document. For example, you can use the ~ sign.
Click the Replace All button. You'll notice that all the list items have been merged together, but don't worry because separators have been placed in the appropriate places (after each item).

Select each element in a separate field. Now that the list items are separated so that they appear in consecutive rows, we need to designate what data will be displayed in each field. For example, if the first line of each element contains the name, the second - the address, and the third - the region and postcode, You can

Press Ctrl + H to open the Find and Replace window.
Remove one of the ^p characters from the Find field.
Change the sign in the Replace with comma field, .
Click the Replace All button. The remaining paragraph marks will be replaced by commas, which will separate each line into a separate field.

Replace the separators to complete the formatting process. Once you've completed the two search and replace steps above, your list will no longer look like a list. All information will be presented in one continuous line, and its fragments will be separated by commas. The final find and replace operation will return your data to a list view, with individual chunks separated by commas.

Press Ctrl + H to open the Find and Replace window.
In the Find what field, enter the ~ character (or whatever character you selected earlier).
In the Replace field, enter ^p.
Click on the Replace All button. All elements of your list will be divided into separate groups, separated by commas.

Save the document as a simple text file. Now that formatting is complete, you can save the document as text file. This will allow Excel application read and analyze the data and place each fragment in the appropriate field.

Go to the File tab and select "Save As".
Open the File Type drop-down menu and select Plain Text.
Enter a name for your file and click the Save button.
If the Convert File window appears, just click OK.

Open the file using Excel. Now that you have saved the file as plain text, you will be able to open it using Excel.

Go to the File tab and select Open.
Open the All Excel Files drop-down menu and select Text Files.
In the Text Wizard (import) window, click Next > .
From the Separators list, select Comma. In the preview window, you will see how your list items will be divided in the table. Click Next >.
Select a data format for each column and click Finish.

Copying a table from Word to Excel is not difficult. To do this, use the Copy and Paste commands, which allow you to conveniently move the entire table with data, or a selected part of it, between applications. A certain complexity arises when the table was created anyhow or was, in turn, copied from somewhere, and when copying it into Excel, extra rows and cells appear.

Well, let's look at the problems that prevent a smooth transfer. The thing is that there is a lot of “garbage” in the table. It could have been typed there when filling tables with data, or transferred when copying information from other applications. As a rule, table deformation is caused by paragraph end marks, which are jammed several times into one cell. It seems that no one has been working on typewriters for a long time, but people still like to move from one line to another using the Enter button.

For example, this situation. The table contains a column “Last name, First name, Patronymic”. In order for this data to be beautifully placed in the column, without pushing it too far apart, when typing, try to place them one below the other, using the Enter button:

Copy this table into Excel

As you can see, in this case, three paragraphs have been created in the cell, each on its own line. As a result of copying such a construction into Excel, we get not one cell, but three, or even four, if the paragraph mark also appears after the middle name.

Table copied to Excel

It's all about the extra characters that were placed in the table cell. Therefore, it is necessary to get rid of them. Let's look at how to do this.

In fact, there is nothing very complicated here, you just need to remove them. Of course, if you do this manually, you can get tired. To do this, you can use Word's character replacement tool.

Since the end of paragraph symbol was in the way, we should remove it. This is done like this:

Cleared Word table

That's it, there are no end-of-paragraph characters in the table now. You can copy it into Excel without worry. The result will be as follows:

Table copied to Excel

It’s the other way around, but still cases of reverse transfer are also not so rare. For example, sometimes you need to transfer a table made in Word to Excel in order to calculate the data using the table editor functionality. Let's find out what methods of moving tables in this direction exist.

The easiest way to move a table is by simply copying it. To do this, select the table in Word program, click right click mouse over the page, and in the appeared context menu select the “Copy” item. You can instead click on the “Copy” button, which is located at the top of the ribbon. Another option involves, after selecting the table, pressing on the keyboard Ctrl keys+C.

So we copied the table. Now we need to paste it into an Excel sheet. Launch the program Microsoft Excel. Click on the cell in the place of the sheet where we want to place the table. It should be noted that this cell will become the top leftmost cell of the table being inserted. This is exactly what you need to proceed from when planning the placement of the table.

Right-click on the sheet, and in the context menu in the insert options, select “Keep source formatting.” You can also insert a table by clicking on the “Insert” button located on the left edge of the ribbon. Or, there is an option to type the key combination Ctrl+V on the keyboard.

After this, the table will be inserted into a Microsoft Excel sheet. The worksheet cells may not match the inserted table cells. Therefore, to make the table look presentable, they should be stretched.

Import table

Also, there is a more complex way to transfer a table from Word to Excel by importing data.

Open the table in Word. Let's select it. Next, go to the “Layout” tab, and in the “Data” tool group on the ribbon, click on the “Convert to Text” button.

The conversion options window opens. In the “Separator” parameter, the switch should be set to the “Tab character” position. If this is not the case, move the switch to this position and click on the “OK” button.

Go to the “File” tab. Select “Save as...”.

In the window that opens for saving the document, indicate the desired location of the file that you are going to save, and also assign it a name if the default name does not satisfy you. Although, given that the saved file will only be an intermediate file for transferring the table from Word to Excel, there is no particular point in changing the name. The main thing to do is to set the “Plain Text” option in the “File Type” field. Click on the “Save” button.

The file conversion window opens. You don’t need to make any changes here, you just need to remember the encoding in which you save the text. Click on the “OK” button.

After this, launch the Microsoft Excel program. Go to the “Data” tab. In the “Get external data” settings block on the ribbon, click on the “From text” button.

The text file import window opens. We look for the file that we saved earlier in Word, select it, and click on the “Import” button.

After this, the Text Wizard window opens. In the data format settings, specify the “Delimited” option. Set the encoding according to the one in which you saved Text Document in Word. In most cases it will be "1251: Cyrillic (Windows)". Click on the “Next” button.

In the next window, in the “Separator character is” setting, set the switch to the “Tab character” position, if it is not selected by default. Click on the “Next” button.

In the last window of the Text Wizard, you can format the data in the columns based on their content. Select a specific column in the Data Parsing Sample, and in the column data format settings, select one of four options:

general;
text;
date of;
skip column.

We perform a similar operation for each column separately. When formatting is complete, click on the “Finish” button.

After this, the data import window opens. In the field, manually specify the address of the cell, which will be the uppermost left cell of the inserted table. If you find it difficult to do this manually, then click on the button to the right of the field.

In the window that opens, simply select the desired cell. Then, click on the button to the right of the data entered in the field.

Returning to the data import window, click on the “OK” button.

As you can see, the table has been inserted.

Above we presented two ways to transfer a table from Word to Excel. The first method is much simpler than the second, and the entire procedure takes much less time. At the same time, the second method guarantees the absence of extra characters or cell displacement, which is quite possible when transferring using the first method. So, to decide on the transfer option, you need to start from the complexity of the table and its purpose.

There are several ways how to insert excel table into word. It all depends on whether you want the inserted Excel spreadsheet in Word was associated with the source file or not. If you are inserting a table from Excel into Word If you link it, it will be updated when changes are detected in the original Excel table. You can also insert a blank Excel spreadsheet in Word and fill it with data, Excel tools from Word. In this article we will look at several ways how to insert a table from Excel into Word.

Insert a linked table from Excel into Word

Let's start with the first method, which is basically just copy and paste table from Excel to Word.

Select an area in your email Excel spreadsheet , which you want to paste into Word, and then press Ctrl+C to copy the contents.

How to insert a table from Excel into Word - Copying a table into Excel

Now go to your Word document and press Ctrl+V to insert table from Excel .

If you want to link a table, that is, so that when you update data in an Excel workbook, the values in the Word document are automatically updated, you must click the " Paste Options" in the lower right corner and select either " Link and keep original formatting", or " Link and Use Destination Styles».

How to insert a table from Excel into Word - Paste Options

If you are using Word 2007 or Word 2003, select one of the following:

How to insert a table from Excel into Word - Paste options in Word 2003, Word 2007

That's it, now if you have linked the table to the original Excel file, whenever you refresh the Excel file and open Word again, then Word will ask you if you want to update the document with the new data from the linked files.

How to insert a table from Excel into Word - Update data from a table in Excel

Click Yes and your table will display the new values.

Using this method you cannot edit the data Excel tables in Word. This method only works in one direction. If you want to edit the original Excel spreadsheet, you can do so from Word by right-clicking on the data, then selecting Related Sheet Object» --> “Edit connection”.

How to insert a table from Excel into Word - Open the table in Excel for editing

This is very useful if someone created this Word document and you don't know where the original Excel file is.

Insert Excel Table

Also, you can insert a fully editable email Excel spreadsheet in Word by going to the " Insert" by clicking " Table" and then clicking the bottom " Excel table ».

How to insert a table from Excel into Word - Inserting an Excel table

This method is only useful if you want to create a new email Excel spreadsheet in Word from scratch and you plan to make changes to the table from Word.

How to insert a table from Excel into Word - Excel Table in Word

You can add sheets, format cells and work with the table as in regular Excel. This is a quick and easy way to insert an Excel table into a Word document that can be formatted correctly using Excel rather than relying on tables Word , which are really difficult to make beautiful.

These are the ways you can insert table from Excel into Word, and link it or not link it to the original Excel table, depending on your needs.

Quite often there is a need to transfer tabular data from a program Microsoft Word to a Microsoft Excel spreadsheet sheet. The table can be copied, but several commands must be run to display the information correctly.

You will need

- Computer;
- installed package Microsoft Office.

Instructions

Open a document created in Microsoft program Word, from which you need to transfer the table to Microsoft Excel. Click on the table with the left mouse button, select the command “Table” – “Select table” from the menu.

Press the key combination Ctrl+C (or the menu item “Edit” – “Copy”; button on the toolbar). Next, go to the worksheet of the book in Microsoft Excel, select the place where the top left cell of the table will be located, press Ctrl+V to copy the Word table into Excel.

Make sure the area you want to copy the table into in Excel is empty, as the data from Word will replace any existing information in the worksheet cells that are in the paste area. Check the dimensions of the inserted table. Next, click the “Insert” button.

To correct the table formatting, click the "Insert Options" button that appears next to the pasted information. If you want to use the formatting that is applied to the worksheet cells, click the "Use destination cell formats" option. If you want to keep the original table formatting, then click on the “Keep original formatting” option.

Edit the resulting table if the table elements were separated by tabs or spaces. To do this, select the table, go to the “Data” menu, select the “Text by Columns” command there.

Select the "Delimited" option, click the "Next" button. Select the desired character (space or tab) as a separator and click the “Done” button. Sometimes after inserting data, you need to clear it in order to be able to use the data calculation function in Excel. For example, unnecessary spaces may appear in cells, and numbers may be inserted in text format rather than numeric format. Dates may also not be displayed correctly. To fix this, select the data of one format, right-click and select Format Cells. In the “Number” tab, set the required data format (numeric, monetary, date, etc.).

Type