Michal Michal - 6 months ago 26
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

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)