Good afternoon,
I run a Power Query daily across approx 1,200 files held on SharePoint, and each file has custom properties based on defined ranges that I am looking to extract. Because of the way the files are held on SharePoint, I cannot drill down into the data any further using Power Query.
The metadata value holds approx 10 values I'm looking to separate out, buried within plenty of other unnecessary data; however due to the way the files work through SharePoint, the locations of each custom value is not fixed in the metadata field. This could be either formula driven or vba.
I've included an amended example, with three example fields that I would be looking to extract (in this example, "Team", "DueDate" & "Manager"). Ideally I want to find a way to identify a pre-determined string (for example "Team:SW|", which could ideally be the column header) then return the remainder of that line ("London").
The closest I've been able to get is using a combined trim/search function, limiting the result to a fixed number of characters (in the example below, 6 characters)
=TRIM(MID(A2,SEARCH("Team:SW|",A2)+LEN("Team:SW|"),6))
but I cannot think of a way to change this formula so it returns the remainder of that string of text regardless of characters before the line break.
Any assistance would be greatly appreciated!
Bookmarks