Hi @lionelh
I'll try to help as this is example were we should think how to try to join different approaches rather than trying to automate everything in PAD/other RPA solutions.
From what you wrote it seems that web page presents the title and company in the same html element, is that correct? (you can use dev tools in yor browser to hover the element and see it in the page code).
So if this is indeed one field, you have 2 options:
Option 1 - Slow (I prefer option 2 )
Extract data to variable, not excel. Then loop through table and for each row, use split text action and use "at" as a delimiter then past the row into excel.
But as mentioned - you need to loop through table and that makes it slower then:
Option 2 - Create blank Excel file and save for use as template: "test.xlsx"
This will be usually much faster than processing Loops
2.1. Let's assume your data you extract looks like this (column names):
[Title and company], [Some other column], [Yet another column]
2.2. Create Excel file with table (important) and these columns:
[Title], [Company],[Title and company], [Some other column], [Yet another column]
2.3. In column [Title] enter formula:
=TRIM(LEFT([@[Title and company]];FIND("at";[@[Title and company]])-1))
2.4 In column [Company] enter formula:
=TRIM(RIGHT([@[Title and company]];LEN([@[Title and company]])-FIND("at";[@[Title and company]])-1))
3. Now build this logic in your flow:
3.1 Open Excel file "test.exe"
3.2 Extract data from web page into variable %ExcelData%
3.3 Past %ExcelData% to Excel you opened in 3.1 into column C, row 2
3.4 Excel will do it's usual magic with formulas in a table: It will expand the table, copy the formulas and will split data for you automatically for all your rows:)
3.5 Save Excel as a new document
3.6 You can now read back to PAD if you need to do something more with the data.
Hope this fits your needs 🙂
