I have a membership spreadsheet with first name, last name, address1, address2, town, state, zip. The spread sheet also includes a partner/spouse (additional name) column and a nickname (additional nickname) column. Except for first name and last name, not all columns have entries.
Ideally, I'd like to combine first name and last name as "lastname, firstname" & any additional name" (combined names). I'd like to combine the address1 (first line), address2, if any, in the second line, town, state zip in the 2nd or 3rd line, where applicable (combined addresses). Lastly, in a new column (combined nickname), I'd like to add nickname & additional nickname.
In the test workbook, I need formulas in 1st three green columns (combined). The yellow columns contain the data from the spread sheet and will have a few other columns such phone, email, etc.
Ideally, I'd like to combine first name and last name as "lastname, firstname" & any additional name" (combined names). I'd like to combine the address1 (first line), address2, if any, in the second line, town, state zip in the 2nd or 3rd line, where applicable (combined addresses). Lastly, in a new column (combined nickname), I'd like to add nickname & additional nickname.
In the test workbook, I need formulas in 1st three green columns (combined). The yellow columns contain the data from the spread sheet and will have a few other columns such phone, email, etc.
testBook.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | First name | Last name | Combined names | Combined nicknames | Combined addresses | Address1 | Address2 | Town | State | Postal Code | Phone | nickname | additional name | additional nickname | ||
2 | Loren | Gordon | Gordon, Loren & Susan | Hiker & Slacker | Kincora Hiking Hostel 1278 Dennis Cove Road Hampton, TN 44106 | Kincora Hiking Hostel | 1278 Dennis Cove Road | Hampton | TX | 44106 | 216-555-9263 | Hiker | Susan | Slacker | ||
3 | Nancy | Gowler | Growler, Nancy | 144 Hillcrest Ave. New Britian, MS 21208 | 144 Hillcrest Ave. | New Britain | MS | 21208 | ||||||||
4 | Mary Eileen | Grevé | Grevé, Mary Ellen | Big Bird | Big Bird | |||||||||||
5 | Maria | Guzman | Guzman, Nancy & Ralph Kramden | Mount Rogers Outfitter 335 E Laurel Ave. Damascus, CO | Mount Rogers Outfitter | 335 E Laurel Ave. | Damascus | CO | 37753 | 423-555-0014 | Ralph Kramden | |||||
Sheet1 |