Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Splitter.SplitTextByAnyDelimiter Show the Delimiter Used

  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    76
    Articles
    0
    Excel Version
    2016

    Splitter.SplitTextByAnyDelimiter Show the Delimiter Used



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Howdi Ho,

    I have a list of Delimiters (Called 'Test') on which I'm then using within a table to split a column called 'Path'. The MCODE is =List.Count(Splitter.SplitTextByAnyDelimiter(Test)([Path]))

    //Ive added the List.Count to basically filter out anything >1 so I can see what values have the been affected by the delimiter.


    My question is, are you able to show the which Delimiter was used to split the Column in the Table from the List 'Test'

    Thanks in Advance

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,449
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I can't think of an easy way to do this. In fact, the only way I can think of is to create a custom function which leverages something like the List.ContainsAny function. You'd have to use the item you're searching as the first (single element) list, and then your delimiter list as the second. The trick would be rolling it up into function that loops through each of those elements in the second list, returning which one.

    So I'm sure it's possible, but it's not going to be easy...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    105
    Articles
    0
    Excel Version
    Office 365
    I tried to create a testable example from your information, but can't get it right. Can you please show the complete M-code.

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    105
    Articles
    0
    Excel Version
    Office 365
    Except for the dynamic assignment of delimiters, I have found a very simple solution that I can show you if you upload a sample folder.

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    76
    Articles
    0
    Excel Version
    2016
    Thanks Ken and pinarello for your responses!

    I will make a simple sample workbook to show you what I'm after. Cheers

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    105
    Articles
    0
    Excel Version
    Office 365
    Attached my solution.

    As you can see, it is a very simple solution. To come up with this idea, I just had to look at the challenge from a different angle.
    Last edited by pinarello; 2021-02-02 at 11:10 PM.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,449
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Nice work pinarello. Love the creative thinking and UI driven approach.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    105
    Articles
    0
    Excel Version
    Office 365
    Hello Ken,

    I will never reach a level of knowledge, in Excel in general and Power Query in particular, that will be comparable to yours. I am all the more pleased with your words of appreciation and feel as if you have knighted me.

  9. #9
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    76
    Articles
    0
    Excel Version
    2016
    Off to play and report back! Thank you pinarello!!!

  10. #10
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    105
    Articles
    0
    Excel Version
    Office 365
    Now the delimiter can also be 2 digits long.

Page 1 of 2 1 2 LastLast

Posting Permissions

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