I again face down my nemesis: Microsoft Excel, the brown sound of business software.
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#SC1203LA1We 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 FunctionPresto! 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).