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
(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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.