Forum Discussion

webportal's avatar
webportal
Impactful Individual
9 years ago
Solved

Search text string within column

I'm trying to find the position (in nr. of characters) of the word "span" in column "order_product_name" of table "order_product".

For that, I'm using a calculated column with the formula:

Coluna 2 = SEARCH("span";'order_product'[order_product_name])

This syntax is exactly the same as used in the DAX reference so I have no idea why it is returning an error.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Interesting...

    I've found this in the DAX reference for SEARCH:

     

    "If the find_text cannot be found in within_text, the formula returns an error. This behavior is like Excel, which returns #VALUE if the substring is not found. Nulls in within_text will be interpreted as an empty string in this context."

     

    So your "IFERROR" takes care of these cases where the "span" is not found.

    I think it's the way to do it.

     

4 Replies

  • Ramadevi's avatar
    Ramadevi
    Frequent Visitor
    9 years ago

    Hi,

     

    Can you try below. 

     

    Created New column with below logic.

     

    SearchColumn = SEARCH("Re",<Column Name>,1,0)

     

    It gave me the position of the word 'Re' 

     

    For example,

     

    Column1     SearchColumnResult

    ----------      ----------------------

     

    I am Ready     6

     

    If the text is not found, i will get 0. Because i mentioned 0 as NotFoundValue parameter

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Maybe a semicolon is the issue....

    Try comma instead:

    Coluna 2 = SEARCH("span",'order_product'[order_product_name])

     

    • webportal's avatar
      webportal
      Impactful Individual
      9 years ago

      Thanks, but no.

      We use semicolons here to separate argument formulas.

       

      I found that if I use:

      Coluna 2 = IFERROR(SEARCH("span",'order_product'[order_product_name]); BLANK())

      It works, although it doesn't make a lot of sense to me.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Interesting...

        I've found this in the DAX reference for SEARCH:

         

        "If the find_text cannot be found in within_text, the formula returns an error. This behavior is like Excel, which returns #VALUE if the substring is not found. Nulls in within_text will be interpreted as an empty string in this context."

         

        So your "IFERROR" takes care of these cases where the "span" is not found.

        I think it's the way to do it.