Looking for a way to remove all text not associated with URLs from a cell. (Office 365)

[Trovek]

Greetings, I am working on a project in I am needing to filter data that is automatically exported to a data table in excel from Microsoft forms. Each cell a particular column (We'll say A) contains up to as many of five links. The problem is, Microsoft Forms formats the exported data in such a way that the links are buried in a single block (With no spaces) of unnecessary text.

For example: [{"name":NameOfFile.xyz","link":"https://LinkToSharePointFile.xyz","id":"IDAssociatedWithUpload","Type":Null":"Size":SizeInMB,"ReferenceID":"LongReferenceID", etc.}]

Cells with multiple links will follow that same format, but the text boxes will loop into one another creating something looking like this.

[{"name":NameOfFile.xyz","link"https://LinkToSharePointFile.xyz","id":"IDAssociatedWithUpload","Type":Null":"Size":SizeInMB,"ReferenceID":"LongReferenceID", etc.}][{"name":NameOfFile.xyz","link":"https://LinkToSharePointFile.xyz","id":"IDAssociatedWithUpload","Type":Null":"Size":SizeInMB,"ReferenceID":"LongReferenceID", etc.}]

I am looking for way to have all of the unnecessary text removed, and have only the links displayed (as clickable hyperlinks).

After doing a little googling, I found a formula that almost works the way I need it to =HYPERLINK(MID(A1,FIND("https",A1),FIND("""",A1,FIND("https",A1))-FIND("https",A1))). The problem is, this will only return the first link in the block of text.

I'm definitely a novice when it comes to excel, so I would appreciate any help in getting this to work the way I am hoping, if that's even possible.

Thanks!

 

[Micron]

I think you will have to use code because of the multiple values in some cells. If that's OK then it presents more opportunities I'd say. I'm not a formula guy so count me out with that. If using code, you'd have to give some idea of the range(s) where this has to be fixed. I imagine that what you show is not really the url - that would make it too simple. If every url was preceded with https and ended with ","id":" that sure would help.

 

[Trovek]

I think you will have to use code because of the multiple values in some cells. If that's OK then it presents more opportunities I'd say. I'm not a formula guy so count me out with that. If using code, you'd have to give some idea of the range(s) where this has to be fixed. I imagine that what you show is not really the url - that would make it too simple. If every url was preceded with https and ended with ","id":" that sure would help.

Hi, thank you for your reply. It does actually seem like I can filter out quite a bit of the unnecessary data before my workbook is updated by adjusting my flow in power automate. I'm definitely a novice when it comes to all of this (which is why I'm here to learn!) and the original format I posted in the OP is a JSON string. I can filter it to include just the link URL, which should make things much easier.

To your comment, each entry does in fact start with https:// and end with ,"id":".

 

[Micron]

So should be doable via code, but if you can remove all that you don't want before inserting it into a sheet, then you're way ahead, I think.

 

[Micron]

this

Sub ExtractURL()
Dim str As String
Dim intStart As Integer, intEnd As Integer

str = Sheets("004").Range("E2")
intStart = InStr(str, "https")
intEnd = InStr(str, """id""") - 2
Debug.Print Trim(Mid(str, intStart, intEnd - intStart))

End Sub

gives me this
https://LinkToSharePointFile.xyz
from this
[{"name":NameOfFile.xyz","link":"https://LinkToSharePointFile.xyz","id":"IDAssociatedWithUpload","Type":Null":"Size":SizeInMB,"ReferenceID":"LongReferenceID", etc.}]