1 Text Functions

In cell D3, add a function to get the two leftmost numbers from cell B3. In cell E3, add a function to get the middle three numbers from cell B3. In cell F3, add a function to get the two rightmost numbers from cell B3. AutoFill the three results down through the remaining products.

1. Click in cell D3
2. Type: =LEFT(B3,2)
3. Click in cell E3
4. Type: =MID(B3,4,3)
5. Click in cell F3
6. Type: =RIGHT(B3,2)
7. Select cells D3 through F3
8. Click the fill handle and drag through row 8

2 Capitalization

Use the appropriate formulas and Autofill to display the names from cells A2 through B4 in cells D2 through E4, using all capital letters. Display the names from cells A2 through B4 in cells G2 through H4, using all lowercase letters. Use cells J2 through J4 to display the length of the text in cells A2 through A4.​

1. Click in cell D2
2. Type: =UPPER(A2)
3. Click the fill handle and drag to cell E2
4. Click the fill handle and drag to cell E4
5. Click in cell G2
6. Type: =LOWER(A2)
7. Click the fill handle and drag to cell H2
8. Click the fill handle and drag to cell H4
9. Click in cell J2
10. Type: =LEN(A2)
11. Click the fill handle and drag to cell J4

3 Combining Text

​Display the combined first and last name from cells A2 and B2 in cell L2, adding a space in between the first and last name. Fill those values for the other two names. In cell N2, combine the values of cells H2 and G2 with a delimiter of a comma and ignoring empty cells. Fill those values for the other two names.

1. Click in cell L2
2. In the Formulas tab, Function Library group, click the Text drop-down arrow
3. Click CONCAT
4. Click cell A2 to populate the Text1 field
5. Click in the Text2 field
6. Type: " "
7. Click in the Text3 field
8. Click cell B2
9. Click the OK button
10. Ensure that cell L2 is still selected and click the fill handle and drag to cell L4
11. Click in cell N2
12. In the Formulas tab, Function Library group, click the Text drop-down arrow
13. Click TEXTJOIN
14. In the Delimiter field type:","
15. Click in the Ignore_empty field
16. Type: TRUE
17. Click in the Text1 field
18. Type: H2
19. Click in the Text2 field
20. Type: G2
21. Click the OK button
22. Ensure that cell N2 is still selected and click the fill handle and drag to cell N4