How to do mailout salutations

In this third and final part of our three-part series helping you to tidy and prepare an e-mailing list we look at generating the salutation.

In Spreadsheet Data 1, we see the sample data which has a variety of records, some more complete than others.

Spreadsheet Data 1

How do we generate a coherent list of salutations from this?

First, we decide on the methodology. If there is a full first name, do we use that instead of the more formal "Mr or Mrs" followed by the surname? In this case, we do. So the first thing to do is to test if there is a first name in the first name column. We also need to check that it is not just an initial, rather than a full name. To do this we will combine the IF function with the LEN function.

In cell D4 we enter the formula

=IF(LEN(B4)>1,B4,"surname")

In plain English this reads as, "If the text in cell B4 (the first name) has more than one character (therefore is not blank nor an initial) , then use it, otherwise, just put in 'surname' (we will deal with these shortly)." We then copy this formula down column D, as in Spreadsheet Data 2.

Spreadsheet Data 2

For those that did not have a first name to use, we now have to determine if there is enough information to put together the more formal salutation using the person's title, e.g. Mr Smith. We ask the spreadsheet if there is a title in the cell in column A again using the IF and LEN functions. We amend the formula in cell D4 to read;

=IF(LEN(B4)>1,B4,IF(LEN(A4)>1,TRIM(CONCATENATE(A4," ",C4)),"Sir / Madam"))

In plain English this reads as, "If the text in cell B4 is longer than one character then use that, otherwise, if the text in cell A4 is longer than one then add that to cell C4, with a space in between (and using the TRIM function to tidy it up in case there are extra spaces in the data entry), and if all else fails, use 'Sir / Madam'". See Spreadsheet Data 3.

Spreadsheet Data 3

Combining this spreadsheet tip with the two previous tips in this series, Removing Trailing Full Stops from Email Addresses and Replacing Commas in Email Addresses , should help you reduce the amount of time it will take you to get your E-mailing list ready for use. If you are thinking of carrying out some Marketing activities, you would be able to generate a targetted e-mailing list at the press of a button with some Bespoke Software from Working Data.

To download this month's spreadsheet tip, click here (Remember to virus-scan all downloads from the internet). Next month we will be offering our newsletter subscribers a VAT calculation tool to help them with the VAT rate change due on January 1st, 2010. If you have any specific problems you would like it to help you with, please contact us.

..back to top