Extract only certain text using web extractor

[lionelh]

Hi,

I am new to Power Automate and was hoping to seek some help on webscraping data. I had extracted data from column A to J.

This was the data that was extracted into an excel in column B. eg

IT Manager at XXX Company

CTO at Z Company

I am trying to remove the "at" text and move the company over to the next column C (which is already) filled. Is there a way that Power Automate could do this?

I tried using web extractor to just select the title but it selects title and company together. 

I'd been searching over the web for info but couldnt really find anything. 

[momlo]

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 🙂

[lionelh]

Hi @momlo 

Seems to make sense for Option 2. Thanks so much for sharing. I was wondering if the fomula is correct as i tried multiple times but once i hit enter, excel will show a error message. Would you happen to know reason?

Thanks so much

[momlo · Accepted solution]

Formula is correct, i guess you need to change ";" to "," or other symbol to match your OS regional settings.

[lionelh]

Thanks @momlo . The fomula now works!

I just wanted to ask if it is possible also to show the trim data instead of a fomula?

[momlo]

Do you mean you would like to have values as if you copy and "paste values" in excel?

Yes, it is possible, so again, no need to automate excel GUI or use shortcuts.

After you paste data in excel, copy all data from the worksheet to the PAD variable and paste it back into the A1 cell, overwriting all data with text. Since PAD reads the text, you will achieve the same result as "past values."

And then, you can remove the "Title and company name" column if you wish, as there will be no formulas using it anymore.

[lionelh]

Thanks @momlo. I tried copying the below using the below actions. which i get it copied, in the fomula bar, it still shows the fomula instead of data. Is there a way to just past values instead of formating?

[momlo]

I just tested on mine:

Copied the cells with formulas

Pasted variable to another Sheet

Got values in new sheet, not formulas

My test flow/File:

SET file TO $'''C:\\tests\\test.xlsx'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: file Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''RAPORT'''
Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''B''' EndRow: 3 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''test'''
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: ExcelData Column: $'''A''' Row: 1

 
Input sheet:

Result:

[VJR]

@lionelh, this will need copying and pasting again as values.

Replace a formula with its result - Microsoft Support

Or activate the cell that contains formula using available action in Excel category.

Sendkeys F2

Sendkeys F9

[momlo]

Hi @VJR 

Not sure why this is not working as expected for @lionelh  but copying data from Excel to PAD variable copies the values, not formulas for me, and in very consistent and reliable way I am able to paste back to Excel without the need to automate gui/sendkeys.

Can you test on your side to see if this is/ is not working for you too?

[VJR]

Hi @momlo 

The issue is happening because you have written a code for Read To Excel and Write To Excel and the code screenshot that @lionelh has shared has got Copy & Paste actions which copies the formulas also.

Hope you got the issue now.

[momlo]

Correct! @VJR - I was reading too fast - thanks 🙂

[lionelh]

Thank you so much @momlo and @VJR. The solution works for me!