When you want to do a mailshot to your client list, it is important that you get the information right first time. Any improper formatting immediately makes your communication seem at worst suspect, and at least, amateurish. Often when you receive client data in a spreadsheet or export it from your CRM system, the data entry hasn’t always been completely perfect. In this month’s spreadsheet tips we look at how to make sure the greeting is neat and tidy using Concatenate and Trim.
It’s newsletter time at Walking Data, our shoe shop. The marketing manager has received the export from the CRM system of all the people who have bought shoes in the last year. Unfortunately, the data entry wasn’t always thorough and the data in the spreadsheet is a bit of a mess. It could take hours and days to get it into a presentable format. By using the Concatenate and Trim functions, he can get the greeting sorted out in seconds.
Spreadsheet Data 1
Spreadsheet Data 1 shows the spreadsheet with the data downloaded directly from the company CRM system. As you can see, the data is in a bit of a mess. There are extra spaces before some of the names, some of the fields are empty. It would take a long time to fix these manually. Instead, we will use the Concatenate and Trim functions to give a clean and correct greeting in seconds.
Spreadsheet Data 2
As in Spreadsheet Data 2, we add a new column to the right, called Greeting. In cell E8 we write
=concatenate(A8,” “,B8,” “,C8,” “,D8)
The concatenate function takes text entries from cells and adds them together into a single entry in another cell. We add spaces between the cells using double quotation marks with a space in between. As you can see the job is not quite done yet, as there are still some extra spaces between the names. We now add the TRIM() function. The TRIM() function removes extra spaces before, between and after words. The formula now reads;
=trim(concatenate(A8,” “,B8,” “,C8,” “,D8))
See Spreadsheet Data 3.
Spreadsheet Data 3
We now have the finished formula and the greeting reads correctly. We copy this down for the other rows, in Spreadsheet Data 4. Check how the formula works for each of the rows regardless of the error in the initial data entry.
Spreadsheet Data 4
With the correct application of Excel’s powerful formulae we have managed to undo the problems created by data entry errors and have saved a great deal of time. This allows the Marketing manager to concentrate on what he does best, creating the message to bring these existing clients back to the shop. He doesn’t have to waste time getting the data into the correct format.
If you have any questions about the Concatenate and Trim functions and when to use them, or if you would like to suggest a topic for us to cover in a future newsletter, please contact us.
