SanfordWhiteman_4-1693255697057.png

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

SanfordWhiteman
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:

SanfordWhiteman_4-1693255697057.png

 

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:

SanfordWhiteman_5-1693255718049.png

 

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:

SanfordWhiteman_2-1693255004766.png

 

In Tools ยป References, check off two non-default references: Microsoft HTML Object Library and Microsoft VBScript Regular Expressions 5.5:

SanfordWhiteman_3-1693255004758.png

 

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

198
0