Analyzing data using text functions
Using some of the text functions that you have learned about in this session, you would like to automate portions of an invoice form to decrease the time needed for data entry.
1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.
https://github.com/alcadelina/Excel-
2. First you would like to find the first initial from the first name that is entered into column A. Click to select cell C2 and type “=LEFT(A2)” into the Formula Bar:
3. Press the Enter key on your keyboard and you will see that the previously selected cell now displays the initial from the first name (“J” in this case):
4. Next, you need to do the same thing and find the first initial from the last name that is entered into column B. Click to select cell D2 and type “=LEFT(B2)” into the Formula Bar:
5. Press the Enter key on your keyboard and you will see that the previously selected cell now displays the initial from the last name (“S” in this case):
6. Now you need to use the TEXTJOIN function to fill in the Full Initials column. Click to select the E2 cell and then type “=TEXTJOIN(,TRUE,C2,D2)” into the Formula Bar:
7. Press the Enter key and you will see that the values from cells C2 and D2 have been combined to show the full initials:
8. Finally, you would like to automatically fill in the shipment method based on the number of characters that appear in the invoice number. If the invoice has more than five characters, then it is designated a rush order and if it is five characters or less, it is a standard order. Select H2:
9. Type “=IF(LEN(G2)>5, "RUSH", "STANDARD")” into the Formula Bar.
Note: The IF function will be covered in greater detail in the next lesson.
10. Press the Enter key on your keyboard and you will see that this particular order is a rush order because its invoice number is more than five characters:
11. Use the Auto Fill feature to copy the formulas that you entered during this activity into the adjacent cells in row 3:
12. Save the current workbook as Activity 2-1 Complete and then close Microsoft 365 Excel to complete this exercise.
13. Now, you can check out an example of a completed document in the link below:
No comments:
Post a Comment