Forum Discussion

hidenseek9's avatar
hidenseek9
Icon for Post Patron rankPost Patron
8 years ago
Solved

How to extract certain words from text?

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

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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