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.
If you would like to read along with the prepared Mailing List Prepartion Spreadsheet Example just follow the instructions below:
Purchase the Mailing List Preparation Spreadsheet Example
Click the button below to purchase the Mailing List Preparation Spreadsheet Example
Once you have completed your purchase via PayPal, you receive a download link via email.
Follow along the Mailout Salutations Example
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.
You can build your own Mailing List Preparation Spreadsheet Example by combining the formulas from these three posts. Alternatively you can take advantage of the time we have already spent preparing ours and purchase one here.
Once you have completed your purchase via PayPal you receive a download link by email.
