SEARCH COLUMN FOR CERTAIN STRING

[aka_krakur]

I have a column of data that looks like this:

Book2
	A	B	C	D
1	PART_NAME
2	FDAABSOL.0358.0MMX20MMX135CM
3	UNKABSOLUTE
4	FDAABSOL.0356.0MMX100MMX135CM
5	FDAABSOL.0356.0MMX60MMX135CM
6	CEABSOL.0358.0MMX80MMX80CM
7	FDAABSOL.03510.0MMX40MMX80CM
8	FDAABSOL.0357.0MMX100MMX135CM
9	CEABSOL.0358.0MMX30MMX135CM
10	CEABSOL.03510.0MMX60MMX80CM
11	UNKABSOLUTE
12	FDAABSOL.0356.0MMX60MMX135CM
13	FDAABSOL.0357.0MMX100MMX135CM
14	FDAABSOL.0358.0MMX60MMX135CM
15	FDAABSOL.0357.0MMX30MMX80CM
16	FDAABSOL.0356.0MMX100MMX135CM
17	FDAABSOL.0357.0MMX100MMX135CM
18	FDAABSOL.0358.0MMX60MMX80CM
19	FDAABSOL.0356.0MMX30MMX135CM
20	FDAABSOL.0358.0MMX100MMX80CM
21	CEABSOL.0357.0MMX60MMX80CM
22	RXACCUNET,5.5,190FDA
23	RXACCUNET,7.5,190FDA
24	RXACCUNET,6.5,190FDA
25	RX.014ACCULINKII6-8/30CE
26	UNKNOWNRXACCUNET
27	5.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?

 

[Scott Huish]

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]

Hi

Try this :

=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]

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]

Slight variation :

=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

 

[Aladin Akyurek]

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]

Maybe...

=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]

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]

Maybe...

=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.