Reference no: EM133165645
Advanced Excel
You have a list of names that are inconsistently formatted (pretend there are 000's of names.) Because of this, you can not use the "text to columns" tool. Rather than retyping the list, you need to look for patterns and figure out a way to determine the first and last names automatically using logical functions (AND, FALSE, IF, IFERROR, IFNA, NOT, and OR, to name a few) and string functions (see below).
Tip: If there is a comma in the cell, then the first name begins after the comma (and a space) and the last name is all of the characters up until the comma. There are many different text functions available such as MID, LEFT, RIGHT, LEN, TRIM, PROPER, UPPER, LOWER, CONCATENATE (just to name a few). Feel free to include additional column(s) that could be hidden to make your formulas easier to write.
The First Name and Last Name columns must be pulled from the same single column. Think about cleaning your data first (in a newly inserted column), then pull both the first and the last names from the new column. Do not delete column, as I want to see how to arrive at the answer.
|
Original Format
|
First Name
|
Last Name
|
Full Name
|
|
Aimee Brown
|
|
|
|
|
Jacob Alexander
|
|
|
|
|
Cynthia Critchard
|
|
|
|
|
Davis, Amanda
|
|
|
|
|
FITCH, MARK
|
|
|
|
|
David Hollingsworth
|
|
|
|
|
Longfellow, Andrew
|
|
|
|
|
brian mark
|
|
|
|
|
Noonan, Carrie
|
|
|
|
|
Putterman, Ryan
|
|
|
|
|
Cyndi Roo
|
|
|
|
|
Rohde, Tina
|
|
|
|
|
Mark May
|
|
|
|
|
Robert, Clay
|
|
|
|
|
Jimmy Neal
|
|
|
|
|
Chad Bruce
|
|
|
|
|
Stockwell, Mike
|
|
|
|
|
joan Pulver
|
|
|
|
|
Moffitt, Melissa
|
|
|
|
|
Tami Evans
|
|
|
|
|
Jeff Farian
|
|
|
|
|
Wayne Swaim
|
|
|
|
|
LUCY BARRETT
|
|
|
|
|
Liam SMITH
|
|
|
|
|
Sophia BROWN
|
|
|
|
|
JONES, Emma
|
|
|
|
|
Madelyn JOHNSON
|
|
|
|
|
Miller, Mason
|
|
|
|
|
Noah WILSON
|
|
|
|
|
Abigail DAVIS
|
|
|
|
|
MOORE, Olivia
|
|
|
|
|
Ava GARCIA
|
|
|
|
|
Jackson THOMPSON
|
|
|
|
|
Ryan MARTIN
|
|
|
|
|
Michael TAYLOR
|
|
|
|
|
Benjamin ANDERSON
|
|
|
|
|
Isabella THOMAS
|
|
|
|
|
white, Jacob
|
|
|
|
|
Lily HARRIS
|
|
|
|
|
Lucas JACKSON
|
|
|
|
|
Chloe MARTIN
|
|
|
|
|
Mia ROBINSON
|
|
|
|
|
Madison lee
|
|
|
|
|
Emily allen
|
|
|
|
|
Ella Young
|
|
|
|
|
Dylan King
|
|
|
|
|
James Hill
|
|
|
|
|
Layla grEEn
|
|
|
|
|
gonzalez, Hailey
|
|
|
|
|
Amelia stewart
|
|
|
|
|
Connor morris
|
|
|
|
|
COOK, Owen
|
|
|
|
|
beLL, Elijah
|
|
|
|
|
Addison murphy
|
|
|
|
|
Zoe rivera
|
|
|
|
|
Jayden cooper
|
|
|
|
|
ALEXander Sanchez
|
|
|
|
|
Edwards, William
|
|
|
|
|
Aubrey PARKER
|
|
|
|
|
Ethan cooper
|
|
|
|
|
Jackson Wood
|
|
|
|
|
Caleb Perry
|
|
|
|
|
Matthewson, Dan
|
|
|
|
|
Logan Bulter
|
|
|
|
|
Avery Jenkins
|
|
|
|
|
prudence yadon
|
|
|
|
|
Winfield, Ramonita
|
|
|
|
|
Rolando Lehr
|
|
|
|
|
Florentio Furlough
|
|
|
|
|
Euna Willison
|
|
|
|
|
Charolette Ceasar
|
|
|
|
|
Norah Fodor
|
|
|
|
|
Caprice Wrench
|
|
|
|
|
oliva Barkley
|
|
|
|
|
Melda Holst
|
|
|
|
|
Lorina Lehman
|
|
|
|
|
Sax Loretta
|
|
|
|
|
Shante Danley
|
|
|
|
|
Cierra Dutra
|
|
|
|
|
Chantelle Ringler
|
|
|
|
|
verna ginsburg
|
|
|
|
|
Cornelison Myrna
|
|
|
|
|
Marlena Paige
|
|
|
|
|
Lennie Reith
|
|
|
|
|
Kiersten Bisceglia
|
|
|
|
|
LEANDRA TALLANT
|
|
|
|
|
Maybelle Seabaugh
|
|
|
|
|
Milford Zuehlke
|
|
|
|
|
Natalia Waid
|
|
|
|
|
sokolowski Sun
|
|
|
|
|
Alix Shedrick
|
|
|
|
|
Ferne farnan
|
|
|
|
|
Mee Bayes
|
|
|
|
|
Cecilia Madore
|
|
|
|
|
Zachery Ruddock
|
|
|
|