Parse multiple fields of metadata

[DystopiaRelic]

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!

[Norie]

Don't know about a formula but perhaps you could use a little UDF.

Please Login or Register  to view this content.

You could use it like this, where the meta data is in A2 and 'Team' is in B1.

=GetMetaData($A$1, B1)

[AliGW]

How about:

=LEFT(MID(A2,SEARCH("Team:SW|",A2)+LEN("Team:SW|"),99),FIND(CHAR(10),MID(A2,SEARCH("Team:SW|",A2)+LEN("Team:SW|"),99)))

=LEFT(MID(A2,SEARCH("DueDate:SW|",A2)+LEN("DueDate:SW|"),99),FIND(CHAR(10),MID(A2,SEARCH("DueDate:SW|",A2)+LEN("DueDate:SW|"),99)))

=LEFT(MID(A2,SEARCH("Manager:SW|",A2)+LEN("Manager:SW|"),99),FIND(CHAR(10),MID(A2,SEARCH("Manager:SW|",A2)+LEN("Manager:SW|"),99)))

But I'm wondering why you're not using PQ for this ...

[phatdear]

Another solution for you

B2=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"|","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[preceding::*[1][contains(.,'"&B$1&"')]]")

C2=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"|","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[preceding::*[1][contains(.,'"&C$1&"')]]")

D2=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"|","</b><b>"),CHAR(10),"</b><b>")&"</b></a>","//b[preceding::*[1][contains(.,'"&D$1&"')]]")

[DystopiaRelic]

Thank you all for the support with this!! I'm continuing to experiment with the three suggested solutions, though I'll continue to attempt to get Power Query to play ball with the way the corporate SharePoint is set up, but at the very least I have a formulaic method of pulling the relevant strings.

[AliGW]

We could help you with the PowerQuery if you shared a suitable sample workbook.