Extracting Smart Campaign/Smart List IDs from a 𝘜𝘴𝘦𝘥 𝘉𝘺 XLS export

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