Michal Michal - 1 year ago 224
JSON Question

VBA delete some colons in column

//:celebrity/:channel[:id='239']/:linear/:schedules[:scheduleId='TV_239_2036-05-11_03:00:00.000' and :startTime='2036-05-11T03:00:00.000+0000']/:authorization[:linearAuth='false' and :linearSubAuth='false' and :authCode='NA']

I have one column with multiple XPaths. I need JSON version for this so I need to delete all colon characters (:) but not in dates (not in scheduleId and startTime). It means delete them only when before colon is: "/" or "[" or " "

How to do that using VBA code? Thanks

Answer Source

Try this... "MyString" is the string with your content "MyReplacer" is what shoud replace the colons (can be space, nothing or any other character)

MyString = "//:celebrity/:channel[:id='239']/:linear/:schedules[:scheduleId='TV_239_2036-05-11_03:00:00."
MyReplacer = " " 'here add what must replace the ":"
MyString = Replace(MyString," :",MyReplacer)
MyString = Replace(MyString,"[:",MyReplacer)
MyString = Replace(MyString,"/:",MyReplacer)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download