CODEWITHSUNDEEP
excelexcel-formula
SIM
SIM

Reputation: 22440

Can't get rid of VALUE error when using excel-formula

I've written a formula in excel which is able to find any specific text from a column, no matter how many times it has appeared. The issue I'm facing is that the cells which doesn't have the result show value error. How can I get rid of this value error? In the following example I tried to find "TORRES" from column A. Using my formula I get that in column B but the value error comes along like shown in the image.

Here is the formula I was trying with:

=IF(A1=SEARCH("TORRES",A1),A1,A1)

Here is the image containing results along with value error:

enter image description here

Btw, When nothing found I want those cells to be blank.

Upvotes: 0

Views: 136

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

You're getting the VALUE error, because there's not always "TORRES" in the column.

You can hide that with IF(ISERR()):

=IF(ISERR(SEARCH("TORRES",A1)),"",A1)

Edit: Updated per your comment under the OP.

This will check if the SEARCH() finds TORRES. If it doesn't, returns "", if it does, it returns the value.

Upvotes: 4

Related Questions