Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
Open the file TextClub in the Student Data Files Excel 2 0 1 9 folder. 2 . Apply the Special Phone Number format to the
Open the file TextClub in the Student Data FilesExcel folder. Apply the Special Phone Number format to the Telephone data in column A Apply the Special Zip Code format not Zip Code to the Zip data in column E Use the Text to Columns command to split the data in the Name column into two columns. Column B should have the first names and column C the last names. In B change the header to First Name and in C Last Name. Change the width of columns A B and C to The member ID is composed of the first initial of the last name, two digits for the birth month and two digits for the day. In the column with the header Birth Month use the MID function to display the two digits of each members birth month. In the column with the header Birth Day use the RIGHT function to display the two digits of that members birth day. In the column with the header Name in Caps use a text function to display the first name entirely in capital letters. In the column with the header Message create the statement below using the data from columns B I and J: Domingos birthday is Use the CONCAT or CONCATENATE function. Make sure you include spacing and appropriate punctuation. Highlight I:L and fill down the Birth Month, Birth Day, Name in Caps, and Message for all the members. AutoFit so all text and the column headings in those columns shows completely columns K and L Create a print header with your first and last name on the left and the current date on the right. Do not use the code for the date; type in the current date yourself. Create a print footer with the sheet name on the left. Use the Header & Footer code for sheet name; do not type the name yourself. Insert two rows above row In A type Text Club. Merge and Center A across A:L Make the title bold thicker characters and size Turn A:L into a table. Name the table with your last name. Apply Red, Table Style Dark Add a new calculated column to the table in column M with the heading First Initial. In M calculate the first initial of each members last name. Add a new calculated column to the table in column N with the heading Initial and Last Name. In N calculate the first initial of the first name, a period, a space, and the last name of each member. For example, the first one will be D Rodriguez. Add a new calculated column to the table in column O with the heading Name, City, State. In O use the TEXTJOIN function to combine the first initial and last name from column N the City from column D and the State from column E Use a comma and a space as the delimiter. Set the ignore empty argument to TRUE. For example, the first one will be D Rodriguez, Amesbury, MA Autofit column O Add the Total Row to the table. Change the label in column A of the Total Row to Counts. Add a count to the Membership ID column and the Initial and Last Name column. Filter the table so only members whose first initial is A B C D S T or W show. Sort by the Member Since column in ascending order oldest to newest Save the file with your last name, first initial, and TextClub eg SmithJ TextClub Submit this workbook using the provided link on the Assignments page by the deadline
Open the file TextClub in the Student Data FilesExcel folder.
Apply the Special Phone Number format to the Telephone data in column A
Apply the Special Zip Code format not Zip Code to the Zip data in column
E
Use the Text to Columns command to split the data in the Name column into
two columns. Column B should have the first names and column C the last
names. In B change the header to First Name and in C Last Name.
Change the width of columns A B and C to
The member ID is composed of the first initial of the last name, two digits for
the birth month and two digits for the day. In the column with the header Birth
Month use the MID function to display the two digits of each members birth
month.
In the column with the header Birth Day use the RIGHT function to display the
two digits of that members birth day.
In the column with the header Name in Caps use a text function to display the
first name entirely in capital letters.
In the column with the header Message create the statement below using the
data from columns B I and J:
Domingos birthday is
Use the CONCAT or CONCATENATE function. Make sure you include spacing
and appropriate punctuation.
Highlight I:L and fill down the Birth Month, Birth Day, Name in Caps, and
Message for all the members.
AutoFit so all text and the column headings in those columns shows
completely columns K and L
Create a print header with your first and last name on the left and the current
date on the right. Do not use the code for the date; type in the current date
yourself.
Create a print footer with the sheet name on the left. Use the Header & Footer
code for sheet name; do not type the name yourself.
Insert two rows above row
In A type Text Club.
Merge and Center A across A:L
Make the title bold thicker characters and size
Turn A:L into a table.
Name the table with your last name.
Apply Red, Table Style Dark
Add a new calculated column to the table in column M with the heading First
Initial.
In M calculate the first initial of each members last name.
Add a new calculated column to the table in column N with the heading Initial
and Last Name.
In N calculate the first initial of the first name, a period, a space, and the last
name of each member. For example, the first one will be D Rodriguez.
Add a new calculated column to the table in column O with the heading Name,
City, State.
In O use the TEXTJOIN function to combine the first initial and last name
from column N the City from column D and the State from column E Use a
comma and a space as the delimiter. Set the ignore empty argument to TRUE.
For example, the first one will be D Rodriguez, Amesbury, MA
Autofit column O
Add the Total Row to the table.
Change the label in column A of the Total Row to Counts.
Add a count to the Membership ID column and the Initial and Last Name
column.
Filter the table so only members whose first initial is A B C D S T or W show.
Sort by the Member Since column in ascending order oldest to newest
Save the file with your last name, first initial, and TextClub eg SmithJ
TextClub
Submit this workbook using the provided link on the Assignments page by the
deadline
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started