If the Automate tab is not displayed on the Ribbon, then activate it as explained in the course notes and during the lecture.The first step is to figure out how to split the data in column A into the parts needed for columns B$,$ C$,$ D$,$ F$,$ G$.$ You need to be comfortable with the formulas before creating the Office Script. Solve each part in the following order:Cell F$3$ needs to contain the character position of the first colon in cell A$3.$ To solve this requirement, use the FIND$()$ function. So for example, if your first line was"Abbot Kramer:Macul: $2010$"The correct answer for the first entry would be $13.$Cell G$3$ needs to contain the character position of the second colon in cell A$3.$ To solve this, you again need to use the FIND function. This time, you need to start the search at the first character after the position of the first colon $($the position you found in cell F$3$ plus one$).$Recall that the FIND$()$ function has three parameters; the first two are required, and the optional third one is the position to start the search. By starting at the character after the first colon $($F$3+1),$ you will find the second colon. For our example string, $("$Abbot Kramer: Macul: $2010"),$ this value will be $19.$Cell B$3$ will contain the name. This will be created using the LEFT function along with the position of the first colon which you already calculated in cell F$3($make sure you subtract $1$ from the value in F$2$ so you do not also take the colon$).$ The answer is the left$12$ characters of cell A$2,$ which for the example entry will be the person's name $-$ Abbot Kramer.Cell D$3$ will contain the Year the user was born. There are technically $2$ ways to do this, and both are acceptable. Both ways start out the same, and will have you using the RIGHT$)$ function. The first way will have you calculate the difference between the length of the string and the position of the second colon. In our example entry $("$AbbotKramer: Macul: $2010").$ Using the LEN$()$ function, you can determine the length to be $23$; subtract the position of the second colon $($in cell G$3)$ and you will have the number of characters to keep from the right side of the first entry. For this first entry, the value will work out to be $4.$What you will notice is that while all the strings in this example will be of different lengths, the difference between the length of the string and the position of the second colon should always be $4$ because the year is always a $4-$digit year. Using this fact, the second way to do this rather than doing the calculation is to always use $4$ as the second parameter of the RIGHT function. The risk in this approach is that a stray space at the end of an entry would result in the wrong answer; this makes the first approach better.

Project$4-$Group$-$Two$-$Question$1$

Input Data $($Name$,$ City Born, Year$)$

Amanda Hickman:Red Springs:$1984$

Amanda Jordan:Greensboro:$2003$

Austin Miller:Hinsdale:$1973$

Brenda Tracy:Alexandria:$1949$

Casey McDonald:Little Rock:$1969$

Cathleen Robertson:St Petersburg:$1947$

Chad Brown:Dallas:$1978$

Charles Baden:Denver:$1992$

Charles Compton:Scappoose:$1946$

Chris Barhorst:Davis:$1965$

Christine Armstrong:Johnstown:$1953$

Darryl Jackson:Smyrna:$1965$

Deborah Brister:San Rafael:$1996$

Debra Brinkman:Irvine:$1946$

Delena Wood:Chicago:$1971$

Dennis Hood:Livonia:$1999$

Diane Anderson:Westminster:$1986$

Domingo McFarland:Austin:$2002$

Donald Wilson:Cambridge:$1951$

Edith Mills:Gardena:$1954$

Edythe Jordan:Columbia:$2001$

Emily Navarro:Garden City:$1975$

Frank McMinn:Centennial:$1998$

Gaynell Haynes:West Newton:$1998$

Grace Durham:Brentwood:$1996$

Grace Lanham:Mount Clemens:$1939$

Harlan Vernon:Doral:$1957$

Harry Baker:Syracuse:$1952$

Heather Adams:Bay City:$1949$

Helen Barrera:Golden Valley:$1976$

James Heller:Columbus:$1954$

Jared Axford:Flint:$1953$

Jeanine Alberts:Secaucus:$1945$

Jeffrey Hensley:Augusta:$1999$

Jerry Ramirez:San Jose:$1955$

Joan Price:Smokey Lake:$1943$

John Alvarez:Kansas City:$1956$

Joseph Gray:San Jacinto:$1961$

Judith Jackson:Milwaukee:$1995$

Judy Perez:Reston:$1982$

Karin Cochran:Stamford:$1944$

Kathryn Gutierrez:Elizabeth:$1977$

Kav Fden:Nnrthamnton:$1976$

D$6$

Kathryn Gutierrez:

A

B

C

E

F

Kay Eden:Northampton:$1976$

Lashandra Owen:Greensboro:$1993$

Lawrence Setliff:Boise:$1990$

Lee Hunt:Independence:$1981$

Leonard Davis:Arlington Heigh; $1960$

Lionel Risner:Philadelphia:$1962$

Lisa Morris:Baton Rouge:$1954$

Louis Barnett:Montpelier:$2002$

Louise Clark:Kill Devil Hills:$1961$

Margaret Campbell:Merrillville: $1996$

Margaret Jenkins:Falls Church:$1944$

Margaret Orange:Austin:$1986$

Mario Forehand:Porter:$1978$

Marion Wright:Pomeroy:$1947$

Marlon Deutsch:Seattle:$1981$

Mary Martin:Panama City:$1961$

Mary Paxton:Appleton:$1987$

Melissa Bowman:Houston:$1991$

Michael Peabody:San Jose: $1945$

Michael Peters:New Bloomfield:$1939$

Michael White:Sioux City: $1940$

Miguel Holt:Westfield:$1965$

Nancy Martinez:West Los Angeles:$1980$

Nancy South:Freeport:$1953$

Otto Amundson:Los Angeles:$1968$

Pat Douglas:Waterville: $1986$

Patricia Brunson:Rale$$gh:$1976$

Paul Oneal:Gallatin Gateway:$1947$

Paula Doyle:Crescent City:$2001$

Ralph Tiedemann:Boise: $1946$

Rebekah Wiggins:Los Angeles:$1969$

R