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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'm guessing that your looking at the end of the description to determine this, 80cm and 135cm is pretty clear but what determines Unknown or Other?
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

Try this :

Code:
=IF(OR(ISNUMBER(SEARCH("80 CM",A1)),ISNUMBER(SEARCH("80CM",A1))),"80 CM",IF(OR(ISNUMBER(SEARCH("135 CM",A1)),ISNUMBER(SEARCH("135CM",A1))),"135 CM","Other"))

HTH, Andrew
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I was thinking UNK, Because sometimes it's UNK and sometimes it's spelled out UNKNOWN. Other would be anything other than 80 CM, 135 CM, or UNK.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432

ADVERTISEMENT

Slight variation :

Code:
=IF(OR(ISNUMBER(SEARCH("80 CM",A1)),ISNUMBER(SEARCH("80CM",A1))),"80 CM",IF(OR(ISNUMBER(SEARCH("135 CM",A1)),ISNUMBER(SEARCH("135CM",A1))),"135 CM",IF(ISNUMBER(SEARCH("UNK",A1)),"Unknown","Other")))

A
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

I was thinking UNK, Because sometimes it's UNK and sometimes it's spelled out UNKNOWN. Other would be anything other than 80 CM, 135 CM, or UNK.

Does

=INDEX({"Unknown";"Other";"80cm";"135cm"},MATCH(ISNUMBER(SEARCH(" CM",A2))+0&ISNUMBER(SEARCH(" 80 CM",A2))+0&ISNUMBER(SEARCH(" 135 CM",A2))+0,{"000";"100";"110";"101"},0))

the job?

Edit. In order to cover cases like 80CM, 135CM, etc...

=INDEX({"Unknown";"Other";"80cm";"135cm"},MATCH(ISNUMBER(SEARCH("*CM",A2))+0&ISNUMBER(SEARCH(" 80*CM",A2))+0&ISNUMBER(SEARCH(" 135*CM",A2))+0,{"000";"100";"110";"101"},0))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
Maybe...

Code:
=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))))

Hope this helps!
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
Domenic; yours worked too. I like it. I'm not familiar with the REPT("z", 255) portion of it though. But it worked. Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Maybe...

Code:
=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))))

Hope this helps!

Will flounder on cases like 180CM or 180 CM... That is, you also need to add a space as prefix.

BTW, it isn't clear whether A22 to A26 are Unknown or Other.
 

Forum statistics

Threads
1,141,681
Messages
5,707,792
Members
421,527
Latest member
Tamiwsw

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
Top