locked
Cannot search for "@" symbol when using search function RRS feed

  • Question

  • I am trying to create a calculated column which extracts the domain part of an email address. My problem is that no type of search formula (SEARCH or FIND) seems to be able to recognise the "@" symbol so I get an error stating that

    "The search TEXT provided to function 'SEARCH' could not be found in the given text"

    Even more bizarre is that if I do just a general search in my table (CTRL + F from inside Powerpivot) it cannot find @ symbols either. Could someone please explain why PowerPivot cannot search for the @ symbol (I haven't tested others) and whether there is a solution?

    The formula I was trying to use was:

    =SUBSTITUTE(A1, LEFT(A1,FIND("@",A1)),"")

    Obviously with A1 replaced by the column name of where my email data is stored.

    Any help would be much appreciated.

    Thanks.

    Thursday, December 12, 2013 2:44 PM

Answers

  • Both FIND() and SEARCH() should find the "@" in PowerPivot.

    I have done it many times.

    You probably have one row in your data set that doesn't contain "@".  That will cause the error you describe and if one row in a Calculated Column causes an error, every row has an error if you do nothing to account for it.

    Try wrapping your formula in an IFERROR([Your Formula], BLANK()).

    This should allow it to run successfully on valid rows and will show a blank on any rows that are causing problems.

    Once you identify the problem row (or rows) you can make a decision about how to deal with it.

    Friday, December 13, 2013 4:14 PM
    Answerer

All replies

  • Both FIND() and SEARCH() should find the "@" in PowerPivot.

    I have done it many times.

    You probably have one row in your data set that doesn't contain "@".  That will cause the error you describe and if one row in a Calculated Column causes an error, every row has an error if you do nothing to account for it.

    Try wrapping your formula in an IFERROR([Your Formula], BLANK()).

    This should allow it to run successfully on valid rows and will show a blank on any rows that are causing problems.

    Once you identify the problem row (or rows) you can make a decision about how to deal with it.

    Friday, December 13, 2013 4:14 PM
    Answerer
  • This formula works for me:

    =IF(IFERROR(SEARCH("Text1",[ColumName]),-1)=-1,"Option1","Option2 or do more logic")

    Wednesday, August 20, 2014 5:56 AM