Trying to extract URL and get it in correct order

[NoobofVBA]

Hello,

I am very new to VBA and I would like to understand how can I get this information in a "readable" URL format from this jumbled code. (pic below).

As you can see the https:// is at the end and the other part is at the beginning. I have tried TEXT to Columns and separated it by " and it does format but breaks it over several columns. I would like to be able to have some type of code (instead of doing this manually which does work by the way if I make it like pic 2) that I could use after I extract the information from SharePoint to remove everything and then create the URL in the correct format (overwriting in the same column), is this possible?

the raw data (pic 1)

And what I would like it to look like. (pic2)

 

[Scott Huish]

Can you give that to us in some sort of text format so that we don't have to retype all that?
Even if it's just copying and pasting from your sheet?

 

[NoobofVBA]

No problem Scott.

Here you go.

{"fileName":"photo.jpg","serverRelativeUrl":"/sites/MicrosoftSpring2026/SiteAssets/Lists/f237a6c1-4ff6-41d0-abe2-09c6002c8996/Erics%20Failure.jpg","id":"3cc097c1-1309-486c-aa6c-4357cecc5efe","serverUrl":"https://microsoft.sharepoint.com","thumbnailRenderer":null}

 

[Artik]

Try to use such a function:

Function ExctractURL(sInput) As String
    Dim v As Variant
    Dim T1 As String
    Dim T2 As String
    
    On Error Resume Next
    v = Split(sInput, ",")
    
    T1 = Trim(Split(v(3), "serverUrl"":""")(1))
    T1 = Trim(Left(T1, Len(T1) - 1))
    
    T2 = Trim(Split(v(1), "serverRelativeUrl"":""")(1))
    T2 = Trim(Left(T2, Len(T2) - 1))
    
    ExctractURL = T1 & T2
End Function

Artik