# Thread: LEFT + SEARCH Function Help

1. ## LEFT + SEARCH Function Help

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

2. Try this one as the formula for a custom column:

Code:
`Text.BetweenDelimiters([Column1], " ", ",", 3, 0)`

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

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. Awesome, looking forward to meeting you as well!

#### Posting Permissions

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