In which I again face down my nemesis: Microsoft Excel, the business software equivalent of classical music outside 7-Eleven.
Admin Β» Field Management Β» Used By supports XLS export when there are too many Used By items to display and/or when you need the data offline for cleanup projects. Alas, the exported files have a frustrating format.
They include a hyperlink to each Smart Campaign/Smart List, but that only works when you click the link within Excel:
If you need the underlying IDs in a text file to import into a database, Save As-ing CSV or TXT wonβt work. Excel ignores the hyperlink and keeps only the visible text in the cell, like keeping the text of an <a> tag, not the href.
(Guess this makes some sense as it needs to keep either the text or the link, not both. Otherwise, youβd get a CSV with more columns than the original XLS, which would be unexpected.)
In any case, we want something like this:
And itβs possible, albeit heart-stoppingly boring, to build a function that extracts just the numeric ID from a URL like:
https://app-sj06.marketo.com/?meue&meueDataOnlyMode&meueVisualCampaign#SC1203LA1
We just need to use a proper URL parser and RegExp engine, both provided via VBA. (No, donβt tell me about how you can use an Excel formula for these tasks. You wonβt get it right!)
Go to Developer Β» Visual Basic Β» Insert and choose Module:
In Tools Β» References, check off two non-default references: Microsoft HTML Object Library and Microsoft VBScript Regular Expressions 5.5:
Finally, paste this code into Module1:
Dim doc As New MSHTML.HTMLDocument
Public Function marketoAssetIdFromURL(ByVal cellValue As Range) As String
Set location = doc.createElement("a")
location.href = cellValue.Hyperlinks(1).Address
assetLocator = location.hash
Set matcher = New RegExp
matcher.Pattern = "^#\D*(\d+)\D*"
Set matches = matcher.Execute(assetLocator)
marketoAssetIdFromURL = matches.Item(0).SubMatches.Item(0)
End Function
Presto! Now you have a custom function you can call like =marketoAssetIdFromURL(A2) and itβll return just the ID of the link in A2. Auto Fill a column with those and youβll be ready to work outside of Excel (whew).
.png)