Replacing an alphanumerical text from a url with a combination of aplha numerical text from the same url

I am stuck with a unique situation where I need to replace an alphanumerical text in an url with a combination of another text in the same url

Here is an example url

web_url("ABC_584","URL=https://ABCD.com/dEKgZETqwmDAh1rP/ArcGIS/rest/services/Overlay_Character/FeatureServer/0/query?

in this url I need to replace "ABC_584" with "Overlay_Character_0"

"ABC_584" is not static and that changes, so anything in the quote after web_url("XYZ_0-9" (i.e XYZ_0-9 in this case), has to be replaced by text after "Service/" and the number after FeatureServer/ from the above url

One more thing "ABC_584" can also be "123_123" completely numerical!

There are 100's of URL which I need to do it for and thus manually not possible, I tried VB script, writing Excel formulas to no use

Anyone here who can help me with this using any of the approach possible via Excel or Python code?

Thanks for reading, do let me know if you need more information

1 answer

  • answered 2020-06-03 02:48 Oliver Leung

    What you want to do can be done in gvim (or vi, vim) editor by a one line regex-replace command (one line for all URLs):

    :%s/web_url(".*","\(.*services\/\(.*\)\/FeatureServer\/\(.*\)\/query\)\@=/web_url("\2_\3","/g
    

    A similar regex can also be done using Python (loop through all your URLs).

    import re
    print (re.sub("web_url\(\"(.*)\",\"(?=.*services\/(.*)\/FeatureServer\/(.*)\/query)",r'web_url("\2_\3","', [your url string]))
    

    If you really want to use Excel formula:

    1. extract the "ABC_584" by =mid([url], 10, find(""",""",[url])-10)
    2. extract the group after "services/" by =LEFT(SUBSTITUTE(MID([url],FIND("services/", [url])+LEN("services/"),1000),"/FeatureServer/","_"), LEN(SUBSTITUTE(MID([url],FIND("services/", [url])+LEN("services/"),1000),"/FeatureServer/","_"))-LEN("/query?"))
    3. Replace (1) with (2) using Excel formula by =SUBSTITUTE([url], [1], [2])