# SEARCH COLUMN FOR CERTAIN STRING

#### aka_krakur

##### Active Member
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
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
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
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
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

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

#### aka_krakur

Yeah, I got it.
Thanks andrew

##### MrExcel MVP

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
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
Domenic; yours worked too. I like it. I'm not familiar with the REPT("z", 255) portion of it though. But it worked. Thanks

##### MrExcel MVP
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.

Replies
2
Views
524
Replies
17
Views
302
Replies
2
Views
84
Replies
2
Views
171
Replies
0
Views
185

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.

### 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