if a cell contains "xxx" function

[Truckman]

I want to create a formula or VBA that can convert a cell depending on a group of codes contained in another cell.

For example

In the cells of column E I have information like the codes shown below.<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 240pt; mso-width-source: userset; mso-width-alt: 11702" width=320><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; WIDTH: 240pt; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=320 height=34>C3300,DD2,EM8,F04,FD1,GE3,GH4,HPL,KH8,KX9,M45</TD></TR></TBODY></TABLE>
I need the cells of column B to search and if code F04 is found somewhere in column E it automatically generates that individual code in column B.

Hope someone can help out with this one, thanks

 

[hiker95]

Truckman,

Excel Workbook
	A	B
1	Auto Generated	Option Codes
2	F20	2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2
3	F07	2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,NH2,QC4,QE2,R38,TR5
4	F07	2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,QE2,R38,TR5
5	F20	2633,AP1,C4500,C63,C93,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5
6	F07	2633,AP1,C3300,C93,EM8,F07,GS3,GT1,KU4,L30,MW5,N52
7	F07	2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5
8	F07	2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5
9	F07	2633,AP3,B99,C4500,C63,C93,EB3,EM8,ES5,F07,GS3,K68,KU4,L30,M45,M49,MW5,QC4,R38,TR5
10	F20	2633,AP1,C4500,C63,C93,EM8,ES5,F20,FZ9,GS3,GT1,KU4,L30,M67,MW5,S28
11	F20	2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2
12	F20	2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2
13	F20	2633,AP1,C4500,EM8,ES5,F20,GS3,GT1,KU4,MW5,NH2
14	F20	2633,AP1,C4500,C63,C93,EM8,ES5,F20,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5
15	F20	2633,AP1,C4500,C63,C93,EM8,ES5,F20,GT1,KU4,MW5,NH2,QC4,QE2,R38,TR5
16	F20	2633,AP1,C4500,EM8,ES5,F20,GT1,KU4,MW5,NH2
17	F20	2633,AP1,C63,C93,EM8,F20,GD3,GS2,KH3,KU4,M45,MW5,MZ9,N56
18		2633,AP1,C63,C93,EM8,GD3,GS2,KH3,KU4,M45,MW5,MZ9,N56
Sheet1

The formula in cell A2 copied down:
=IF(ISERROR(FIND(",F",B2,1)),"",IF(FIND("F",B2,1)>0,MID(B2,FIND("F",B2,1),3),""))

 

[Truckman]

Basically this list is a truck stock list, all the information in the cell is the options fitted to a truck. The F-codes designate what cabs are fitted to each vehicle. What i'm trying to do is setup a column next to this that decides what type of cab is fitted to the vehicle depending on what code is shown in the cell next to it. It can change for each vehicle the cab and corrseponding code is shown below.

F07 - Short cab
F05 - Mid cab
F04 - Long cab

From there I can setup an autofilter column for customers to use so they can shorten the list down to show only vehicles containing the cab they are after. (The full version of this list is over 1000 rows in length)

Hope this makes sense?

 

[mikerickson]

So which one should be shown?

 

[Blade Hunter]

So as I am reading it, you want to show F07, F05 or F04 if they are found? Can we assume that anything starting with an F is a cab and should be shown? (Which is what Hikers formula does)

 

[Truckman]

no, thats where it gets confusing, some trucks have codes such as F33 which means they have airdeflectors on the roof aswell of FH9 can mean the cab has a woodgrain dash.

A truck with a lot of options selected can look like the one below but i'm trying to filter out the cab size only and leave the other codes out of it.

2651 LS,AP3,C3300,DD2,ES5,EV5,F04,FD1,FH0,FQ2,FR9,GE3,GH8,J76,KY9,L16,LG1,MU6,NH2,RL6,SA5,SAFETY

Apologies if the explanations are a bit confusing I have a reasonable understanding of excel but some functions are new to me

 

[mikerickson]

It sounds like a range of columns, one for each variation, might be useful.
Each truck would have a check mark in one of those columns (or not) depending on if that truck had that feature.

If the Headers for the variation columns are in AA1:AH1
AP1 AP3 C3300 C4500 EM8 ES5 F20 GS3

You could put
=ISNUMBER(FIND(AA$1,$B1) in AA2 and drag it to AH:1000 and it will return TRUE/FALSE whether that feature is in the string in column B.

Number formatting can turn that difference into a checkmark.