CODEWITHSUNDEEP
excelsearchformula
user7954
user7954

Reputation: 323

Excel: How can I search a string for a number greater than something?

I'm in need of categorizing some items from our system where it's currently not available. They are TVs and have a description such as:

Sony 55in Pro Display w/ LED

One of the categorizations I want is for any TV over 32 inches. Is there anyway to do a search in the string for >32&"in" or >32&" in" or >32&"in." etc.

I'll be working with user-entered data so would need to do a few different checks..

Upvotes: 0

Views: 1231

Answers (1)

AwokeKnowing
AwokeKnowing

Reputation: 8226

if the string Sony 55in Pro Display w/ LED is in A1, use

VALUE(MID(A1, SEARCH( "in", A1 ) -2,2))

it should give the number 55. go from there.

Note that this doesn't account for 3 digit numbers. if you are sure no screens will be 1 digit (eg 9 inches) then just use

VALUE(MID(A1, SEARCH( "in", A1 ) -3,3))

Now obviously the product could have the letters "in", so then you'd maybe want to check if the resulting value is greater than zero etc. So first, have the value end up in another column, and verify that each row is producing the right number, and get more specific if needed.

Upvotes: 2

Related Questions