# check if text doesn't exist

#### BORUCH

##### Active Member
HI all

I need an excel formula
If a cell containseither the word "LB." or "LBS." but it doesn't contain, with itin the same cell the word “OZ" or "oz", then it should be blank.

Thanks

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### igold

##### Well-known Member
Two things.

1) You are missing a condition, what should the cell be if it does not match your criteria as stated?

2) you do realize that a cell can contain either a formula or a value but not both.

igold

#### BORUCH

##### Active Member
Two things.

1) You are missing a condition, what should the cell be if it does not match your criteria as stated?

2) you do realize that a cell can contain either a formula or a value but not both.

igold

=IF(ISNUMBER(SEARCH("LBS",G112)),"",IF(ISNUMBER(SEARCH("LB",G112)),"",IF(AND(OR(ISNUMBER(SEARCH("LBS",G112)),(ISNUMBER(SEARCH("LB",G112)))),ISNUMBER(SEARCH("OZ",G112))),LEFT(G112,LOOKUP(2^15,SEARCH({"X","-"},G112))-1),F112)))

Above is my formula the problem is it is not working,
What i'm trying to accomplish is if the cell contains only the word "lbs" or ONLY the word "lb" then it should be blank if it contains the word lbs or the word lb AND IN THE SAME CELL IT ALSO HAS THE word "oz" OR "OZ" then ,LEFT(G112,LOOKUP(2^15,SEARCH({"X","-"},G112))-1

#### igold

##### Well-known Member
The cell cannot both contain a formula and be blank if a condition is met. It is a circular reference.

##### MrExcel MVP
HI all

I need an excel formula
If a cell containseither the word "LB." or "LBS." but it doesn't contain, with itin the same cell the word “OZ" or "oz", then it should be blank.

Thanks

Care to post some examples (input) along with the desired results, i.e. desired output?

Replies
1
Views
180
Replies
7
Views
227
Replies
10
Views
174
Replies
3
Views
53
Replies
3
Views
252

1,190,959
Messages
5,983,855
Members
439,867
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back