SEARCH COLUMN FOR CERTAIN STRING

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I have a column of data that looks like this:
Book2
ABCD
1PART_NAME
2FDAABSOL.0358.0MMX20MMX135CM
3UNKABSOLUTE
4FDAABSOL.0356.0MMX100MMX135CM
5FDAABSOL.0356.0MMX60MMX135CM
6CEABSOL.0358.0MMX80MMX80CM
7FDAABSOL.03510.0MMX40MMX80CM
8FDAABSOL.0357.0MMX100MMX135CM
9CEABSOL.0358.0MMX30MMX135CM
10CEABSOL.03510.0MMX60MMX80CM
11UNKABSOLUTE
12FDAABSOL.0356.0MMX60MMX135CM
13FDAABSOL.0357.0MMX100MMX135CM
14FDAABSOL.0358.0MMX60MMX135CM
15FDAABSOL.0357.0MMX30MMX80CM
16FDAABSOL.0356.0MMX100MMX135CM
17FDAABSOL.0357.0MMX100MMX135CM
18FDAABSOL.0358.0MMX60MMX80CM
19FDAABSOL.0356.0MMX30MMX135CM
20FDAABSOL.0358.0MMX100MMX80CM
21CEABSOL.0357.0MMX60MMX80CM
22RXACCUNET,5.5,190FDA
23RXACCUNET,7.5,190FDA
24RXACCUNET,6.5,190FDA
25RX.014ACCULINKII6-8/30CE
26UNKNOWNRXACCUNET
275.5RXACCUNET,190CE
Sheet1


In column B I need a formula, with an end result that tells me whether A is a 80cm or a 135cm, Unknown, or "Other"

Is there a formula like =isnumber(search)...that could do this?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Will flounder on cases like 180CM or 180 CM... That is, you also need to add a space as prefix.

Thanks Aladin! Much appreciated! I missed that completely... :oops: So the formula should be corrected as follows...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"Other",INDEX({"80 CM","135 CM","Unknown"},MATCH(TRUE,ISNUMBER(SEARCH({" 80*CM"," 135*CM","UNK"},A1)),0))))
 
Upvote 0
More on this search string formula

I had to go back to the drawing board due to a Part_Name that had both 80/135 in it which it defaulted to 80 CM because it was the 1st in the string (I'm not sure why but I have no control over the Part_Name field..it's just pulling in from a query).
Anyway. I used to determine 80 CM or 135 CM based off the Part Number.
Here's an example of the spreadsheet with the Part_Number column:
Book1
ABCDE
1PRODUCT_FAMILYPART_NUMBERPART_NAME80,135(CE,)Part_no
2Omnilink35Biliary1008180-28OTWOMNI357.0MMX28MMX80CMOther1008180
3RXAccunet1011334-55RXACCUNET,5.5,190FDAOther1011334
4Omnilink35Biliary1008182-38OTWOMNI358.0MMX38MMX80CMOther1008182
5Absolute351010566-20FDAABSOL.0358.0MMX20MMX135CM135CM1010566
6RXAcculink1010133-30RX.014ACCULINKII7-10/30CEOther1010133
7RXAccunet1011334-55RXACCUNET,5.5,190FDAOther1011334
8Agiltrac351010008-40AGILTRAC358.0MMX40MMX80CMOther1010008
9Absolute35UNKAbsoluteUNKABSOLUTEOtherUnknown
10Absolute351010566-20FDAABSOL.0358.0MMX20MMX135CM135CM1010566
11Absolute35UNKAbsoluteUNKABSOLUTEOtherUnknown
12Absolute351010564-100FDAABSOL.0356.0MMX100MMX135CM135CM1010564
13Absolute351010564-60FDAABSOL.0356.0MMX60MMX135CM135CM1010564
14Absolute351010533-40CEABSOL.0357.0MMX40MMX80CM80CM1010533
15Absolute351010534-30CEABSOL.0358.0MMX30MMX80CM80CM1010534
16Absolute351010565-100FDAABSOL.0357.0MMX100MMX135CM135CM1010565
17Absolute351010542-30CEABSOL.0358.0MMX30MMX135CM135CM1010542
18Absolute351010563-80FDAABSOL.0355.0MMX80MMX135CM135CM1010563
19Absolute35UNKAbsoluteUNKABSOLUTEOtherUnknown
20Absolute351010564-60FDAABSOL.0356.0MMX60MMX135CM135CM1010564
Sheet1

Okay, here's a breakdown of what I did in the last two columns:
Column E (Part_No) formula:
Start with Row 10
Code:
=IF(ISERROR(VALUE(LEFT(B10,7))),"Unknown",VALUE(LEFT(B10,7)))
Column D, Row 10
Code:
=IF(ISERROR(IF(AND(E10>=1010531,E10<=1010536),"80 CM",IF(AND(E10>=1010539,E10<=1010544),"135 CM",IF(AND(E10>=1010555,E10<=1010560),"80 CM",IF(AND(E10>=1010563,E10<=1010568),"135 CM","Other"))))),"Unknown",(IF(AND(E10>=1010531,E10<=1010536),"80 CM",IF(AND(E10>=1010539,E10<=1010544),"135 CM",IF(AND(E10>=1010555,E10<=1010560),"80 CM",IF(AND(E10>=1010563,E10<=1010568),"135 CM","Other"))))))
That formula came from a breakdown I found out on the PART_NUMBER range.
80 CM CE : 1010531 THRU 1010536
135 CM CE : 1010539 THRU 1010544

80 CM FDA: 1010555 THRU 1010560
135 CM FDA: 1010563 THRU 1010568

Does anyone know an easier formula that I could get the same results.
I can't seem to include the above formula in a VB Macro (too long or something?????)

Please help.
 
Upvote 0
How about...

=IF(N(E10),IF((E10>1010531)*(E10<1010536),"80 CM CE",IF((E10>1010539)*(E10<1010544),"135 CM CE",IF((E10>1010555)*(E10<1010560),"80 CM FDA",IF((E10>1010563)*(E10<1010568),"135 CM FDA","Other")))),"")
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top