How to remove html tags from some rows.

[RBettinelli]

Hello
I have a report that has web addresses in it. The addresses that go to a SharePoint site are fine, but the ones that point to a document have <Div class="external....... stuff before and after the web address. Everything I have tried (custom columns, extracts etc) have ended up stripping out the rows that are fine. How do I fix just the rows that are problems and leave the rows that are fine?

[RBettinelli · Accepted solution]

I was able to make a work around my changing my colomn type in the SharePoint list itself.

[Anonymous]

Hi @RBettinelli ,

Maybe you can try formula like below:

ExtractedURL = 
IF (
    CONTAINSSTRING ( 'Table'[URL], "div" ),
    VAR StartPosition =
        FIND ( "href", 'Table'[URL] ) + 6
    VAR EndPosition =
        FIND ( ">", 'Table'[URL], StartPosition )
    RETURN
        MID ( 'Table'[URL], StartPosition, EndPosition - StartPosition-1 ),
    'Table'[URL]
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AmiraBedh]

Can you share your data ? an example of the rows containing html tags?

[RBettinelli]

 I've had to change it a bit so I can share it, but its basically this.

Data Sources

https://collaboration.ca/sites//SitePages/Home.aspx?web=1

https://collaboration.ca/sites//SitePages/Home.aspx?web=1

https://collaboration.ca/sites//SitePages/Home.aspx?web=1

https://app.powerbi.com/groups/me/apps/cf8d9210-8aaa-4e69-a991-a5073bcf2509/reports/b95a573b-fcc0-43...

https://collaboration.ca/sites//SitePages/Home.aspx?web=1

null

https://app.powerbi.com/groups/me/apps/cf8d9210-8aaa-4e69-a5073bcf2509/dashboards/ec65c01e-89a6-4d5b...

https://collaboration.ca/sites/PI%20Dashboards%20%20Reports/Program%20Inventory%20Summary%20Dashboar...

https://collaboration.ca/sites/PI%20Dashboards%20%20Reports/Program%20Inventory%20Detailed%20Report%...

https://collaboration.ca/sites/DRPRM-DPRGR/Lists/L1%20Contact%20List/AllItems.aspx?viewid=16da80ca-3...

<div class="ExternalClass48A2FB7E4CCF4A14BD26D4E5E63F255E"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><a href="https&#58;//collaboration.ca/sites/Public%20Documents/Forms/AllItems.aspx?csf=1&amp;e=jvpUGa&amp;web=1&amp;RootFolder=/sitesR/Public%20Documents/Business%20Planning/BP%20Cycle%20Docs/BP%202024-25/L1%20Subs&amp;FolderCTID=0x012000DA5BB5821A5BB54BA8E22E8C99761EC8">Directorate Resource Planning &amp; Risk Management - DRPRM - L1 Subs - All Documents ()</a><br></div></div>

<div class="ExternalClassEB63DF80908F47A8B9317FF87A2D1554"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><a href="https&#58;//collaboration.ca/sites/Public%20Documents/Forms/OE%20Dir.aspx?csf=1&amp;e=OcVPoh&amp;RootFolder=/sitesPublic%20Documents/Organization%20and%20Establishment%20Directive%20-%20Directive%20sur%20L%e2%80%99organisation%20et%20les%20Effectifs&amp;FolderCTID=0x01200054A6647BE5A4FE46B4C01EFC6010CC02">Director Defence Force Planning (DDFP) - Organization and Establishment Directive - Directive sur L’organisation et les Effectifs - O&amp;E Dir ()</a><br></div></div>

<div class="ExternalClass8DD2F5CACECA410B8697A81B3D4E5513"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><a href="https&#58;//collaboration.ca/sites/DGDFP/DDFP/MOO/Forms/Default.aspx">MOO - Default ()</a><br></div></div>

<div class="ExternalClass1C1786A275B341298D0081D2D0CC3550"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><a href="https&#58;//collaboration.ca/sites/Public%20Documents/Forms/AllItems.aspx"> - Public Documents - All Documents ()</a><br></div></div>