+ Reply to Thread
Results 1 to 6 of 6

Parse multiple fields of metadata

  1. #1
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Parse multiple fields of metadata

    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!
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Parse multiple fields of metadata

    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)
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Parse multiple fields of metadata

    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 ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Registered User
    Join Date
    12-04-2020
    Location
    Bangkok
    MS-Off Ver
    365
    Posts
    61

    Re: Parse multiple fields of metadata

    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&"')]]")
    Attached Files Attached Files
    Last edited by phatdear; 12-17-2020 at 11:41 AM. Reason: Modified the formula to become slightly shorter

  5. #5
    Registered User
    Join Date
    07-27-2020
    Location
    Manchester, UK
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Parse multiple fields of metadata

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,198

    Re: Parse multiple fields of metadata

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 15
    Last Post: 03-16-2020, 12:13 PM
  2. Replies: 0
    Last Post: 04-17-2015, 06:01 AM
  3. VBA to parse a text string containing three fields of different lengths
    By rupsidhu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2015, 09:01 PM
  4. Replies: 5
    Last Post: 11-21-2014, 10:00 PM
  5. I need Help to parse address city state zip and country if applicable for all fields
    By danpotash in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2013, 03:16 PM
  6. How to parse date/time stamp into different fields?
    By chrishornbeck in forum Excel General
    Replies: 1
    Last Post: 05-07-2008, 12:39 PM
  7. [SOLVED] Import Pipe Delimited File, Parse out certian Fields, create new f
    By StarBoy2000 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-17-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1