Extracting Smart Campaign/Smart List IDs from a ๐˜œ๐˜ด๐˜ฆ๐˜ฅ ๐˜‰๐˜บ XLS export

Level 10 - Community Moderator
Level 10 - Community Moderator

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:


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).