'loop through every cell in the selected range For i = 0 To - 1 'Set the variable of the number of columns in the table The VBA code below which will cycle through the selected data and recreate a table layout.ĭim NoOfColumns As Integer Dim TargetRow As Integer Dim TargetCol As Integer Dim i As Integer 'Set the initial values for the variables of where to place the table If the paste into Excel is just a list of values in one column we can turn to VBA for a bit of help. It may not be perfect, but it’s still closer than what you had before. Or maybe, which is more likely, you have a table which requires a bit of tweaking. You may now have a perfect data table in Excel. However, Word is actually better at dealing with PDF tables. Here is our example PDF file: Copy table from PDF to Excel via WordĮxcel is a software program which is designed to work with tables, whilst Word is designed to work with text. One of these should work, providing reasonable results and saving you time. I would love to offer you the perfect solution to get the PDF data table into Excel, however I don’t believe there is one. If the paste displays as a picture, then as far as I know, you will need to resort to third-party software which contains OCR. If the paste is either a list of values or a continuous text string, then there are some possible workarounds. In my experience the pasted data will show as one of the following: The pasted information will be displayed in Excel differently based on how the PDF was created. Pasted Strings: Use VBA to split the string.Pasted strings: Use Excel functionality to split the string.Pasted list: Use VBA to format the list.Please refer to this article for more information about the INDEX formula in Excel. Example: In cell C10, the INDEX formula returns the value from the first row and first column of the range A1 to G7. The third column contains the actual values, pulled by the formula =INDEX($A$1:$G$7 A10 B10).Also for the column number you always start with number one.
The screenshot on the right-hand side shows the necessary preparations. That way you can easily refer to the original table.
Basically you have to create the column and row number in additional helper columns. But on the other hand, it’s one of the faster ways (compared to setting up the more complex OFFSET formula like in method 3 below or the INDIRECT formula). Unfortunately, it requires some preparations. You can convert a two-dimensional table into just one column by using the INDEX formula.
0 kommentar(er)
