Spreadsheet: 802535d1666899152-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] | 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>.