Need help with finding text in a cell.

Please Login or Register  to view this content.

Hi

Will something like this work for you

Attachment 261659

welcome to the forum n3xas. a better explanation would be to show the desired results for the different scenarios
150x150x6x12000 S355
contains both "x6x" & ends with 355. what should it show? 615?

150x150x5x12000 S355
does not contain "x6x" & ends with 355. what should it show? 610?

150x150x5x12000 S255
does not contain "x6x" & ends with 255. what should it show? 562

if i'm correct, then:
=IF(COUNTIF(B39,"*x6x*"),615,IF(RIGHT(B39,3)="355",610,562))
otherwise, let us know the answers to the above scenario and other scenarios you have.

an explanation of the formula you have:
=IF(ISNUMBER(SEARCH(235; B67)); 562; (IF(ISNUMBER(SEARCH(X6X;B67)); 615; 610)))
if 235 is found anywhere in B67, put it as 562.
the second search isn't valid because you need to put double quotes when searching for texts like "x6x". so if i put the double quotes, it will return 615 if "x6x" is found in B67
only when the 2 tests fails, it will return 610. so changing it to this will work (if i assume correctly):
=IF(ISNUMBER(SEARCH(235, B39)), 562, (IF(ISNUMBER(SEARCH("X6X",B39)),615, 610)))

These are steel pipes. 150x150x6x12000 are the dimensions and S355 shows how strong it is, therefore it is more expensive than 255. (610 instead of 562). If it is 355 and thicker (6mm instead of 5 or 4)the price goes to 615. This does not apply to S235. So:

Originally Posted by benishiryo

welcome to the forum n3xas. a better explanation would be to show the desired results for the different scenarios
150x150x6x12000 S355
contains both "x6x" & ends with 355. what should it show? 615?

Yes, 615.

150x150x5x12000 S355
does not contain "x6x" & ends with 355. what should it show? 610?

Yes, 610

150x150x5x12000 S255
does not contain "x6x" & ends with 255. what should it show? 562

Yep.

an explanation of the formula you have:
=IF(ISNUMBER(SEARCH(235; B67)); 562; (IF(ISNUMBER(SEARCH(X6X;B67)); 615; 610)))
if 235 is found anywhere in B67, put it as 562.
the second search isn't valid because you need to put double quotes when searching for texts like "x6x". so if i put the double quotes, it will return 615 if "x6x" is found in B67
only when the 2 tests fails, it will return 610. so changing it to this will work (if i assume correctly):
=IF(ISNUMBER(SEARCH(235, B39)), 562, (IF(ISNUMBER(SEARCH("X6X",B39)),615, 610)))

Thanks a lot, I will try that and let you know how it goes.

EDIT: this did the trick, so I was only missing the quotes around X6X. Though I'm still a bit confused about ISNUMBER, can you use it in any similar situation or not. The description says it only checks if the value is a number, so how does it work here

Anyway, thanks everyone.

Originally Posted by n3xas

Though I'm still a bit confused about ISNUMBER, can you use it in any similar situation or not. The description says it only checks if the value is a number, so how does it work here

using your eg of this in B39:
=IF(ISNUMBER(SEARCH(235, B39)), 562, (IF(ISNUMBER(SEARCH("X6X",B39)),615, 610)))
the SEARCH formula returns the starting position of whatever you are looking for. so when you are looking for 235, the SEARCH formula alone gives you a #VALUE error. you can try this separately on another cell:
=SEARCH(235, B39)
that's because 235 cannot be found in B39. the ISNUMBER here is to determine if whatever you are searching for can be found. otherwise, your formula would just stop & give you a #VALUE error. so in this case, the ISNUMBER will say that #VALUE is not a number & gives you FALSE. when it's FALSE, it will proceed to the next IF. and that is to search for "X6X" in B39. this time, it finds it & return you position number 8. again, you would need the ISNUMBER to return TRUE or FALSE. because it is now a number, it will return you the value you desire; 615.

if that answers your questions, do mark this thread as "Solved"