Open the file TextClub in the Student Data Files$$Excel $2019$ folder.

$2.$ Apply the Special Phone Number format to the Telephone data in column A$.$

$3.$ Apply the Special Zip Code format $($not Zip Code $+4)$ to the Zip data in column

E$.$

$4.$ 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$1$ change the header to First Name and in C$1$ Last Name.

$5.$ Change the width of columns A$,$ B$,$ and C to $15.$

$6.$ 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 member$$s birth

month.

$7.$ In the column with the header Birth Day use the RIGHT function to display the

two digits of that member$$s birth day.

$8.$ In the column with the header Name in Caps use a text function to display the

first name entirely in capital letters.

$9.$ In the column with the header Message create the statement below using the

data from columns B$,$ I and J:

Domingo$$s birthday is $04/15/2020$

Use the CONCAT or CONCATENATE function. Make sure you include spacing

and appropriate punctuation.

$10.$ Highlight I$2$:L$2$ and fill down the Birth Month, Birth Day, Name in Caps, and

Message for all the members.

$11.$ AutoFit $($so all text and the column headings in those columns shows

completely$)$ columns K and L$.$

$12.$ 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.$)$

$13.$ 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.$)$

$14.$ Insert two rows above row $1.$

$15.$ In A$1$ type Text Club.

$16.$ Merge and Center A$1$ across A$1$:L$1.$

$17.$ Make the title bold $($thicker characters$)$ and size $16.$

$18.$ Turn A$3$:L$63$ into a table.

$19.$ Name the table with your last name.

$20.$ Apply Red, Table Style Dark $3.$

$21.$ Add a new calculated column to the table in column M with the heading First

Initial.

$22.$ In M$4,$ calculate the first initial of each member$$s last name.

$23.$ Add a new calculated column to the table in column N with the heading Initial

and Last Name.

$24.$ In N$4,$ 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.$)$

$25.$ Add a new calculated column to the table in column O with the heading Name,

City, State.

$26.$ In O$4,$ 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$.)$

$27.$ Autofit column O$.$

$28.$ Add the Total Row to the table.

$29.$ Change the label in column A of the Total Row to Counts.

$30.$ Add a count to the Membership ID column and the Initial and Last Name

column.

$31.$ Filter the table so only members whose first initial is A$,$ B$,$ C$,$ D$,$ S$,$ T$,$ or W show.

$32.$ Sort by the Member Since column in ascending order $($oldest to newest$).$

$33.$ Save the file with your last name, first initial, and TextClub $($e$.$g$.$ SmithJ

TextClub$).$

$34.$ Submit this workbook using the provided link on the Assignments page by the

deadline