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

manasaMS

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

Jeff Reisman

@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)