Forum Discussion
Division into Lines
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
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
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:
I need this data "CCCCC" in cell 'A3'. Is it possible?
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:
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.
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:
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.
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.
