I want to return a Cell's value in IFERROR Function if the value is wrong
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

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 leftmost 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
Categories
Check out the Formula Handbook template!