PLAY THE NAME GAME

Many people use Excel spreadsheets for maintaining address lists. But the number of columns it takes to create a "full name" for a mailing address label can vary greatly from user to user. After all, you can maintain an address list with as few as five columns: Name, Address, City, State, and Zip. But with this approach, you can't sort your list by last name.

That's why most users split up the name into at least two discrete columns: First_Name (column A) and Last_Name (column B). Creating a full name based on those fields is easy with a formula like +A2&" "&B2.

But suppose the spreadsheet uses five columns to compose the full name: Salutation, First_Name, Middle_Initial, Last_Name, and Title. The problem with this layout is that you don't always have entries in the Salutation, Middle_Initial, and Title columns. So how do you create a full name column based on these five columns?

Assuming the salutation, first name, middle initial, last name, and title columns are stored in columns A through E, respectively, you can use the following formula for the first line of your mailing labels:

The CONCATENATE portion of this formula creates a string that combines everything in columns A, B, C, and D with spaces in between each entry. Then, if there's anything in column E, it adds a comma to that string, a space, and the value that's in column E. By wrapping the TRIM function around the results of the CONCATENATE function, you eliminate any extraneous spaces generated when the Salutation and Middle_Initial fields are blank.

Created Date: 03/20/2002  Last Reviewed: 03/20/2002  Rev. Date: