How to extract certain words from text?

[hidenseek9]

Hello Power BI Community,

I have a question regarding extracting certain words out from text.

Please find below as the dummy data created.

Sample Data

From this dummy data, I would like to do 3 things.

1. If a text under column "Name" contains a word "Greek", I would like to create a column called "Type" and list "Greek" in it.
2. If a text under column "Name" contains a size "1P/3P/4P/6P/8P", I would like to create a column called "Pot" and list the pot size. (if it is 1P sometimes, a text does not even contain "1P" in it, just as the very last example under "Name")
3. If a text under column "Name" contains a word "CO_", I would like to create a column called "Costco" and list Costco in it.

The final output that I desire is the screenshot above. 

There is no consistancy in the texts, but is there a way to achieve my goal?

Many thanks,

H

[v-huizhn-msft · Accepted solution]

Hi @hidenseek9,

Please create calculatec column using the formulas below.

Type =
IF (
    IFERROR ( SEARCH ( "Greek", Sheet1[Name] ), 0 ) = 0,
    BLANK (),
    RIGHT (
        LEFT ( Sheet1[Name], IFERROR ( SEARCH ( "Greek", Sheet1[Name] ) + 4, 0 ) ),
        5
    )
)

Pot =
IF (
    IFERROR ( SEARCH ( "1P", Sheet1[Name] ), 0 )
        + IFERROR ( SEARCH ( "3P", Sheet1[Name] ), 0 )
        + IFERROR ( SEARCH ( "4P", Sheet1[Name] ), 0 )
        + IFERROR ( SEARCH ( "6P", Sheet1[Name] ), 0 )
        + IFERROR ( SEARCH ( "8P", Sheet1[Name] ), 0 )
        = 0,
    BLANK (),
    RIGHT (
        LEFT (
            Sheet1[Name],
            IFERROR ( SEARCH ( "1P", Sheet1[Name] ), 0 )
                + IFERROR ( SEARCH ( "3P", Sheet1[Name] ), 0 )
                + IFERROR ( SEARCH ( "4P", Sheet1[Name] ), 0 )
                + IFERROR ( SEARCH ( "6P", Sheet1[Name] ), 0 )
                + IFERROR ( SEARCH ( "8P", Sheet1[Name] ), 0 )
                + 1
        ),
        2
    )
)


Costco =
IF ( IFERROR ( SEARCH ( "CO_", Sheet1[Name] ), 0 ) = 0, BLANK (), "Costco" )

Please see expected result as follows, you can download attachment file for more details.



Best Regards,
Angelia

[hidenseek9]

@v-huizhn-msft

Oh my goodness.

This is working beautifully. Amazing.

Thank you so much!

H