Spreadsheet: 802538d1666899918-need-a-specific-formula-to-find-two-different-words-in-a-column-homework
Sheet: Start Here

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Data Analysis Exercise 1
Please read the following carefully and then answer the below 8 questions.  Every part fitted to a vehicle needs to have a part number to enable correct part ordering (amongst many other important reasons). Table 1 below contains data relating to 10 part numbers. These part numbers are allocated to different variants of car seat.  The sheet called "Definitions & Functions" contains:      - definitions that you will need to know in order to answer the below questions.       - 6 basic Excel Functions. Several of these Functions (possibly combined) will be needed to answer questions 1-5.   Important Notes: - Questions 1-5 will require formulas as answers. These formulas should be created in the first row and dragged down to all rows. ie.must be the same generic formula in each row - Questions 6-8 will require text answers only. No formulas or functions required. -  To be fair to people with varying Excel backgrounds, you're only allowed to use the functions listed in the 'Definitions & Functions' sheet
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Available points | 0.5 | 0.5 | 1 | 2 | 2 | 1 | 1 | 2
Table 1
PART NUMBER | [NAME] | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column102 | Column103 | Column104 | Column105 | Column106 | Column107 | Column108 | Column109 | Column110 | Column111 | Column112 | Column11 | DRIVE | MINOR FEATURE CODES | 1 PREFIX | 2 BASE | 3 SUFFIX | 4 Base | 5 RHS/LHS | 6 SEAT DRV/PASS | 7 | 8
8L47-C822O26-KMIX | RHS FRONT SEAT ASY 2WAY COMP |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CXSBC CZQBH CZ2BB DQNBC HDEBC 3ZB11 |  |  |  |  |  | DRV | CZ2BB
9L47-C822M26-BFX | FRONT SEAT ASY COMP RHS 2WAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV8BC CWSBD CXTBB CZRBC CZ3BB CZ6BK DQOBC JCQBE 3YW11 |  |  |  |  |  | PASS
6L47-C822N27-GLKX | FRONT SEAT ASY COMP LHS 4WAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | [NAME] CXSBC CZQBE CZ2BB DQNBC HDEBD 3D311 |  |  |  |  |  | DRV
9L47-C822O26-RKRX | RHS FRONT SEAT ASY COMP 6WAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CZ2BB DQNBC HDEBD 3ZB11 |  |  |  |  |  | PASS
9L47-C822N26-FHX | FRONT SEAT ASY RHS COMP 4WAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | P | BBHBU BBHBZ CV7BC CWSBD CXSBB CZQBE CZ2BB DQNBB HDEBD 3YW11 |  |  |  |  |  | DRV
9L47-C822N26-RRTX | FRONT SEAT ASY COMP RHS 4WAY |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | CV8BC CWSBD CXTBC CZRBE CZ3BB CZ6BK DQOBB JCQBE 3ZB11 |  |  |  |  |  | DRV
9L47-C822N29-MMDX | 4WAY FRONT SEAT LHS ASY COMP |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV7BC CWSBD CXSBC CZQBE CZ2BC DQNBC HDEBC 3K011 |  |  |  |  |  | PASS
9L47-C822N28-ADAX | FRONT SEAT 4WAY ASY COMP RHS |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CXSBB CZQBE CZ2BC DQNBB HDEBD 3YW11 |  |  |  |  |  | DRV
9L47-C822N29-GGX | 4WAY LHS FRONT SEAT ASY COMP |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBC HDEBC 3YW11 |  |  |  |  |  | DRV
8L47-C822N29-KMX | 4WAY FRONT LHS SEAT ASY COMP |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Q | BBHDG CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBB HDEBD 3K011 |  |  |  |  |  | PASS
Questions
Question 1 | Create a formula in column <E> that extracts the PREFIX only from column A
Question 2 | Create a formula in column <F> that extracts the BASE only from column A
Question 3 | Create a formula in column <G> that extracts the SUFFIX only from column A
Question 4 | YOU HAVE BEEN MISINFORMED! Apparently the BASE section of a part number can vary between 6 and 8 digits. (and is not always 7 digits as mentioned in the Definitions & Functions sheet).  Even though the above part numbers all contain 7 digit BASES, create a formula in column <H> that shows the BASE, but could also handle varying lengths.  (You can re-use your formula from Question 2, if it already handles this scenario).
Question 5 | The Vehicle Seat part numbers are defined as LHS or RHS. This information is shown within the [NAME] in column <B>.  Create a formula in column <I> that extracts the LHS or RHS text only.
Question 6 | Using only the information in columns <B> & <C>, confirm in column <J> whether each seat is a DRIVER or PASSENGER seat. (Note, no formulas required for this question)
Question 7 | The string of MINOR FEATURE CODES shown in column <D> helps to define the specifc usage of the relevant part number. If the string contains the MINOR FEATURE CODE [CZQ**]  eg. CZQBD, CZQBE,CZQBH, CZQBM or CZQBU, then the part number is a driver seat  If the string contains the MINOR FEATURE CODE [CZR**]  eg. CZRBC, CZRBE, CZRBL, [NAME] or CZRBV, then the part number is a passenger seat  One Seat part number has a clear data error in Table 1. Highlight which part this is, in the appropriate cell in column <K>.
Question 8 | Now to fix the problem part number found in Question 7: Firstly, it's been confirmed that the data in column <B> and column <C> in Table 1 is definitely correct. Secondly, the sheet "Part Number Base Definition" shows how part number BASES are initially defined according to seat features. (Note, this is not always a 1 to 1 relationship) Using the above information, determine the correction that is required to fix the data error. Show this in column <L>.
PART NUMBER | [NAME] | Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 | Column11 | DRIVE | MINOR FEATURE CODES | 1 PREFIX | 2 BASE | 3 SUFFIX | 4 Base | 5 RHS/LHS | 6 SEAT DRV/PASS | 7 | 8
8L47-C822O26-KMIX | RHS |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CXSBC CZQBH CZ2BB DQNBC HDEBC 3ZB11 | 8L47 | C822O26 | KMIX | C822O26 | RHS | DRV | CZ2BB
9L47-C822M26-BFX | RHS |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV8BC CWSBD CXTBB CZRBC CZ3BB CZ6BK DQOBC JCQBE 3YW11 | 9L47 | C822M26 | BFX | C822M26 | RHS | PASS
6L47-C822N27-GLKX | LHS |  |  |  |  |  |  |  |  |  |  |  | Q | [NAME] CXSBC CZQBE CZ2BB DQNBC HDEBD 3D311 | 6L47 | C822N27 | GLKX | C822N27 | RHS | DRV
9L47-C822O26-RKRX | RHS |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CZ2BB DQNBC HDEBD 3ZB11 | 9L47 | C822O26 | RKRX | C822O26 | RHS | PASS
9L47-C822N26-FHX | RHS |  |  |  |  |  |  |  |  |  |  |  | P | BBHBU BBHBZ CV7BC CWSBD CXSBB CZQBE CZ2BB DQNBB HDEBD 3YW11 | 9L47 | [NAME] | FHX | [NAME] | RHS | DRV
9L47-C822N26-RRTX | RHS |  |  |  |  |  |  |  |  |  |  |  | Q | CV8BC CWSBD CXTBC CZRBE CZ3BB CZ6BK DQOBB JCQBE 3ZB11 | 9L47 | [NAME] | RRTX | [NAME] | RHS | DRV
9L47-C822N29-MMDX | LHS |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV7BC CWSBD CXSBC CZQBE CZ2BC DQNBC HDEBC 3K011 | 9L47 | C822N29 | MMDX | C822N29 | RHS | PASS
9L47-C822N28-ADAX | RHS |  |  |  |  |  |  |  |  |  |  |  | P | [NAME] CXSBB CZQBE CZ2BC DQNBB HDEBD 3YW11 | 9L47 | C822N28 | ADAX | C822N28 | RHS | DRV
9L47-C822N29-GGX | LHS |  |  |  |  |  |  |  |  |  |  |  | Q | BBHBU BBHBZ CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBC HDEBC 3YW11 | 9L47 | C822N29 | GGX | C822N29 | RHS | DRV
8L47-C822N29-KMX | LHS |  |  |  |  |  |  |  |  |  |  |  | Q | BBHDG CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBB HDEBD 3K011 | 8L47 | C822N29 | KMX | C822N29 | RHS | PASS