PLAY THE NAME GAME
Many people use Excel spreadsheets for maintaining address lists. Butthe 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 discretecolumns: 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, andtitle columns are stored in columns A through E, respectively, you can use the following formula for the first line of your mailing labels:
=TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2,IF(E2>"",", "&E2,"")))
The CONCATENATE portion of this formula creates a string that combineseverything 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: