I want to return a Cell's value in IFERROR Function if the value is wrong

Options

Hi All,

I want to return a Cell's value in IFERROR Function if the value is wrong.

In the above screenshot i have some values. I want return the word before @gmail.com in the Primary column, and if the value does not contain @gmail.com(Eg: Orange) I want to return the value as it is in the primary column.

thanks

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @manasaMS

    To accomplish this, we'll use an IF formula containing the LEFT and FIND functions.

    =IF(FIND("@", [Primary Column]@row) > 1, LEFT([primary Column]@row, (FIND("@", [Primary Column]@row) - 1)), [Primary Column]@row)

    In English: if the @ symbol in found in the cell, then find the position of the @ symbol and take everything to the left of it; if the @ symbol is not found in the cell, then just take the contents on the cell as it is.

    In your Apple@gmail.com example above, the @ symbol is found in position 6 in the text. We tell the LEFT function to give us the left-most characters of text up to one position left of the @ symbol (position 6, minus 1, equals 5.) So we get "Apple" as the result.

    Alternatively, you can test for the presence of the @ symbol by using CONTAINS function instead, but you'll still need FIND to determine the position of the symbol in the value:

    =IF(CONTAINS("@", [Primary Column]@row), LEFT([primary Column]@row, (FIND("@", [Primary Column]@row) - 1)), [Primary Column]@row)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!