Division into Lines

Copper Contributor

Hello,

 

 

I use de Adobe to pass tables form *.pdf to *.xlsx.

 

But, in some of this cases, this passage isn´t very good. The problem that I have now, is: when the Adobe transported the tables to excel, it put a lot of lines in the same cell.

 

See what happened:

 

11 INT PAG **bleep** 175036339799           215,00-                    38.70028.1* 4175 454 15081i 5251 1              28 I
EB 70028 993070028   396.    98587                 I
11 TAR/CUSTAS COBRANCA                 88,42-                    40.09152.1       679 1508li 0692 1              28 I
11 CEI     000686 DINHEIRO            560,00                     60.80317.lA 0064 454 150811 0002 1                 I
CA 80317 007119431   421.    50006                 I
11 CEI     000687 DINHEIRO            780,00                     60.80317.lA 0064 454 1508li 0002 1                 I
CA 80317 007119431   421.    50007                 I
11 TRANSF  6615.07636-8            79.022,09                     74.09125.1       635 15081Ô 0761 1                 I
11 APL APLIC AUT MAIS               3.179,01-            150,00  32.09152.2        687 150812 0552 1              28 I
12 SISPAG SALARIOS                    600,00 -                    32.09133.1  6615 667 150813 1106 1              28 I
12 SISPAG SALARIOS                     17,60-                    32.09133.1  6615 667 150813 1106 1              28 I
12 SISPAG FORNECEDORES             21.583,27-                    32.09133.1  6615 667 150813 1106 1              28 I
12 TAR/CUSTAS COBRANCA                 68,76-                    40.09146.1       679 150812 0695 1              28 I
18.377,02                     74.09114.1        635 150811 0757 1                 I

 

All information is in the same cell, with lines and columns separated by spaces..
Does anybody know how can I divide just the lines? I only know the process to pass in columns.

 

Thank's 

 

7 Replies

@DagostiniExcel 

maybe Power Query can help. If you have Excel 2013  or higher, you can have Power Query in Excel.

A screenshot of the PDF would be helpful so that we can understand where the row breaks.

Thanks,

Celia

And there is also this feature that would allow you to take a picture of the PDF with your cell phone and have it transferred to an Excel Spreadsheet. I am not sure this is already available, but I thought it would be nice to share:

http://www.iphonehacks.com/2019/03/convert-pictures-printed-data-table-microsoft-excel-ios.html

@Celia_Alves,

PDF connector is for PBI Desktop and is not available for Get & Transform. Mobile solution is the OCR, thus not reliable, and still for Insiders.

 

Perhaps resulting Excel could be adjusted, but from the post text it's not clear, better to check sample Excel file.

@Sergei Baklan 

Correct! I was not suggesting to load the data to Power Query (Get & Transform) through a PDF connector. Depending on how "regular" the data is, it might be faster to transform it with Get & Transform than with formulas. We'll have to see how the sample comes.

@Celia_Alves 

 

The Adobe transport all data to excel, but some times the program insert a lot of informations that should be in different lines into the same cell.

 

Look the document in PDF:

 

 

image.png

@Celia_Alves 

 

The transport of data, insert in the same cell diferent information, that should be in diferent collumns and lines.

 

When different collumns insert in same cell I use the "Text to Columns", and the problem is solved.

 

But when the program insert diferent lines in the same cell, and I use the "Text to Columns", the data are passed as they were in the same line

 

image.png

 

 

My question is: The Excel can put the letters "CCCCC" in the 3º line? If I use "text to columns" the letter "CCCCC" will go to 'G2', LOOK:

 image.png

 

I need this data "CCCCC" in cell 'A3'. Is it possible?

 

 

 

@DagostiniExcel 

If you use Get and Transform (Power Query), you can

click the button "Split Column" 

choose by delimiter

choose Custom for the delimiter and enter what your delimiter is (that's the challenging part here)

Click advanced options

Choose "Rows"

I ended up choosing the special character Line Feed to be able to split the rows.

Then, I replaced "space space" with "!"

Then, I replaced "!!" by "!" consecutive times until I only had no more than one ! in a row.

Then, I used "!" as a delimiter to slipt by columns.

I was able to get data to look like this:

dagostini.JPG

You can use the file attached to paste your data into cell A1 of sheet 1 and then go to sheet 2, tab Data, button Refresh All.

The table in sheet 2 should get updated with the result of the steps created in Power Query applied to your data.

Maybe then you can copy and paste as values somewhere else and massage it a bit more to get what you need.

I hope this helps.