Forum Discussion

Reuben's avatar
Reuben
Iron Contributor
May 12, 2017
Solved

HOW TO: "If cell contains specific text then return specific text"

I'm trying to extract a bunch of specific text combinations from cells and present it in a new cell. This formula seems to work for two variables but I can't add any more variables too it. =IFERROR...
Formulas & Functions
Requests
  • SergeiBaklan's avatar
    SergeiBaklan
    May 14, 2017

    I'm sorry but i see no limitations which could prevent to use nested IF. The only point it is a bit less flexible compare to nested If equivalents.

     

    But what to use depends on goals, in some cases quick hardcording works quite fine.

     

    If use formatting nested if becomes much more clear and editable. Like this

    =IF(ISNUMBER(SEARCH("Sales",        B3,1)),"Sales",
     IF(ISNUMBER(SEARCH("Arch",         B3,1)),"Architecture",
     IF(ISNUMBER(SEARCH("Land",         B3,1)),"Land",
     IF(ISNUMBER(SEARCH("All",          B3,1)),"All",
     IF(ISNUMBER(SEARCH("Contracts",    B3,1)),"Contracts",
     IF(ISNUMBER(SEARCH("Construction", B3,1)),"Construction",
     "No Match"
     ))))))

     

    If instead of hardcoded strings use references nested IF becomes more flexible. And if add some extra references (nested if) for future strings to find it becomes even more flexible.

     

     =IF(ISNUMBER(SEARCH($F$1,B3,1)),$G$1,
  IF(ISNUMBER(SEARCH($F$2,B3,1)),$G$2,
  IF(ISNUMBER(SEARCH($F$3,B3,1)),$G$3,
  IF(ISNUMBER(SEARCH($F$4,B3,1)),$G$4,
  IF(ISNUMBER(SEARCH($F$5,B3,1)),$G$5,
  IF(ISNUMBER(SEARCH($F$6,B3,1)),$G$6,
  IF(ISNUMBER(SEARCH($F$7,B3,1)),$G$7,
  IF(ISNUMBER(SEARCH($F$8,B3,1)),$G$8,
  IF(ISNUMBER(SEARCH($F$9,B3,1)),$G$9,
    "No Match"
 )))))))))

    I don't vote for nested IF,  i would like to say where is no limitations here. What to use that's concrete person choice.

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 13, 2017

Ah, a classic one.

 

Key words in column F, returned text in column G, text to check in A1.

 

=LOOKUP(PI(),1/COUNTIF(A1,"*"&$F$1:$F$6&"*"),$G$1:$G$6)

 

DMColleran's avatar
DMColleran
Copper Contributor
Nov 04, 2020
Is there an equivalent way to use this Excel logic in Power Query were the source list of text string is a table or another query?
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 04, 2020

    DMColleran 

    Not sure what exactly you'd like to do, perhaps something like

    = Table.SelectRows(
  #"Table To Filter" ,
  each List.Contains(
         #"Table With Filter"[ColumnWithValues],
         [ColumnOnWhichWeFilterSourceTable]
        )
)

     

    • DMColleran's avatar
      DMColleran
      Copper Contributor
      Nov 04, 2020

      SergeiBaklan we have a data cleanliness issue, whereby a freeform field contains key information about each entry. Inside the freeform field are keywords which we want to pull out into a new column. The =LOOKUP(PI(),1/COUNIF(TargetCell,"*"&KeywordRange&"*"),KeywordRange) Formula works well in an Excel sheet, however our dataset is much larger and we will need to handle it in PQ or some other tool. Essentially we want perform the same operation as the above function but within PQ. 

       

      I have attached a sample of the dataset, with tables showing the raw data and target keywords (INPUTS) as well as a sample output of cleaned data (OUTPUT). I achieved the example output by using a fuzzy match in PQ, but that is not necessarily a perfect resolution. Perhaps it works. 

       

      Cheers,  

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Nov 04, 2020

        DMColleran 

        I' not sure how the table with keywords works. For example, keyword a is met in every word for several times. Thus simply try to reproduce what LOOKUP() do. Second part of your query, instead of fuzzy merging, is 

            #"Lowercased Text" = Table.TransformColumns(
        #"Removed Columns",
        {{"Description", Text.Lower, type text}}
    ),
    KeywordsTable = CannaFormKeywords,
    #"Add Cannabis Form" = Table.AddColumn(
        #"Lowercased Text",
        "Cannabis Form",
        each
            let
                txt = [Description],
                lst = KeywordsTable[Keyword],
                out = KeywordsTable[Cannabis Form],
                transformed = List.Transform(
                    lst,
                    each
                        if Text.Contains(txt, _)
                        then out{List.PositionOf(lst,_)}
                        else null
                ),
            cleaned =
                List.Last(
                    List.RemoveNulls(
                        transformed
                    )
                )
            in
                cleaned),
    #"Remove source texts" = Table.SelectColumns(
        #"Add Cannabis Form",
        {"ID", "Date", "Cannabis Form"}
    )
in
    #"Remove source texts"

        (see duplicated query). 

        Result looks the same as with fuzzy match

         

         

         

Resources