Results 1 to 6 of 6

Thread: LEFT + SEARCH Function Help

  1. #1

    LEFT + SEARCH Function Help



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

    Hello. I am very new to Power Query.

    I have a formula I use in a normal excel worksheet:

    =LEFT((MID(A1,SEARCH("( ",A1)+3,200)), (SEARCH(", S",(MID(A1,SEARCH(" (",A1)+3,200)))-1))

    It takes the following string in cell A1:

    DV1511H, Episode Name ( Show Name, SeriesNumber)

    ...and displays "Show Name" i.e. I'm trying to extract the Show Name from the above string

    DV1511H, Episode Name ( Show Name, SeriesNumber) =LEFT((MID(A1,SEARCH(" ( ",A1)+3,200)), (SEARCH(", S",(MID(A1,SEARCH(" ( ",A1)+3,200)))-1))
    DV1511H, Episode Name ( Show Name, SeriesNumber) Show Name

    Is there a way to create a similar formula in Power Query ?

    Many thanks

    Thomas
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,119
    Articles
    57
    Blog Entries
    14
    Try this one as the formula for a custom column:

    Code:
    Text.BetweenDelimiters([Column1], " ", ",", 3, 0)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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
    Many thanks, Ken. I'll try that when I get home.
    By the way, I'm really enjoying your book M Is For Data Monkey. I'm still near the beginning of the book and am looking forward to becoming more proficient.

    Thanks again
    Thomas

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,119
    Articles
    57
    Blog Entries
    14
    Cool stuff, and I hope you find it useful!

    Just an FYI, you won't find this function in there - although you will find a sections that shows how to use the MID and SEARCH equivalents.

    I'm actually (right now) creating a blog post to show how I got this specific formula though.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

  5. #5
    My suggestion would be:

    Code:
    Text.BetweenDelimiters([Column1], " ( ", ", S")
    Instead of using the new functionality of adding a column by example, you can use the Add Column - Extract - Range option with some dummy starting index and number of characters, next adjust the code to the code above. This is a more direct translation of the original Excel formula.

    As a matter of fact, I created a 14 minute video about the 3 new functions that were added in the April 2017 update of Power BI Desktop: Text.AfterDelimiter, Text.BeforeDelimiter, Text.BetweenDelimiters.

    @Ken Puls: looking forward meeting you next week Wednesday, April 19!

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,119
    Articles
    57
    Blog Entries
    14
    Awesome, looking forward to meeting you as well!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    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.

Posting Permissions

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