previous icon Back to blog
Oct 13, 2015
8 minutes read

How to Send Personalized SMS Messages Using Excel

Personalizing the contact you have with your customers is very important. It shows dedication, you have prepared a personal message, especially for him or her.

In research recounted in The Small BIG, by Robert Cialdini, Steve Martin, and Noah Goldstein, it is shown that this can cut no-shows by 43%. Simply adding someone’s first name to the message will boost the response to the message. This is also supported by our own experiences of reducing no-show in the health care sector. 

How to Send SMS Directly From Your Excel File

The CM.com platform allows you to upload a Microsoft Excel file with phone numbers and send an SMS message to those numbers. To do this you can click the “Send from Excel”-button on your “Send Messages”-page. 

How to send personalised SMS

You can also add the text of the actual message to the Excel file and thus send a different message to each recipient. In this way, you can personalize the information. To do this we select the column with the text of the message in step 2 of the import process. You can find some more background information on sending from Excel files in our Help Center.

Today we take personalization to a next level by also adding names, gender, and even time from your CRM into the message body to create a 100% personalized message for each recipient. 

Excel file uploaded including message body

Preparing Your Messaging List in Excel to Send a 100% Personalized SMS

This part is about a few nifty Excel tricks that help you merge personal information into your messages. Say you want to confirm appointments for your personal finance business. Your customers expect a personal invitation as well as the date and time of their appointment with you. 

We start with this basic information in our Excel file. Probably this is an export from your CRM tool.

Example export from your CRM tool of choice

And our goal is to send the following message to the recipients in this file.

“Dear Mr. John Doe, your appointment is scheduled for November 1, 2015 at 11:00. Best regards, Cas Personal Finance”

The first thing we do is add an additional column in which we will compose our message body. It is this column we will upload to the CM.com platform.

Add message body column

Using the Concatenation Function in Excel

The most important Excel function we will be using here is concatenation. Basically, this function allows us to paste together different text strings into one, making personalization possible.

So the formula:

=CONCATENATE("Hello"," John")

will resolve into the text “Hello John”. Make sure you tell Excel that these are text strings by adding quotes (“ “) around the text string. Also, do not forget to add spaces as Excel will not add them for you.

Example of concatenation in Excel

The cool thing here is that you can also combine the input of two cells. So if I insert “hello” in A1 and “John” in A2 I can combine them by using the following formula:

=CONCATENATE(A1,A2)

And this will give us “hellojohn”. Apparently, we forgot to add spaces :).. adding that will make it look a lot better:

=CONCATENATE(A1," ",A2)

This formula tells Excel to take the input of cell A1, add a space (quotes to indicate a text string, and the space is the only text in this string i.e. “ “), and add the contents of cell A2 to that to give us “hello john”.

Concatenation: do not forget to add spaces

Merging Data for Professionals – 100% personalization

Let us see if we can take this to the next level. Adding this all together we can make the basic layout of our text message.

=CONCATENATE("Dear ",C2," ",B2,", your appointment is scheduled on ",D2," at ",E2,". Best regards, Cas Personal Finance")

Basic data merge for SMS personalisation

That might look like a complex formula, but if you look at each of the different parts it is nothing different from what we’ve done before: combining the contents of different cells and adding static text strings (and spaces !!) to that.

Getting the Time Right

Now we only need to solve a few layout problems, the date and time are pasted in as numerical values. Excel has its own notation system for date formats and time formats.

Dates in Excel are represented in a number that counts the days since January 1, 1900. So November 3, 2015 is 42311 days after the start of the 20th century. 

Working with dates in Microsoft Excel

Times in Excel work the same. If “1” is an entire day (ie. 24 hours), “0,5” is 12 hours and each hour adds 0.041666667 (1 / 24) to that number. Makes sense right?

Working with time  in Microsoft Excel

So we need to tell Excel that it should use a human-readable version of the date and time. To do this we can use the formula TEXT:

=TEXT(D2,"mmmm d, yyyy")

Here we tell Excel to get the date value from D2 and format it in the way we want. “mmmm” means write the full month (ie. “November” instead of “11”). “d” means write the day and “yyyy” means to write the full year (ie. “2015” instead of “15”).

And we can do the same with the time:

=TEXT(E2,"hh:mm")

And if we add this to our existing formula we get

=CONCATENATE("Dear ",C2," ",B2,", your appointment is scheduled on ",TEXT(D2,"mmmm d, yyyy")," at ",TEXT(E2,"hh:mm"),". Best regards, Cas Personal Finance")

Formatting dates and times in Excel to personalise messages

A Proper Salute: Mr. or Mrs. in Excel

Almost there! The last thing we need to ensure is that we translate the “m” or “f” into “Mr.” or “Mrs.”. There are many ways to do this. Here we are going to solve it with the IF formula. The IF works as follows:

=IF("a logical test","value if true","value if false")

So we can say; that if the value in column C is “f”, you should use “Mrs.”, if it is false use “Mr.”.

=IF(C2="f","mrs.","mr.")

And in this way, we can complete our message, with the appropriate salutation, good looking dates and fully personalized.

=CONCATENATE("Dear ",IF(C2="f","mrs.","mr.")," ",B2,", your appointment is scheduled on ",TEXT(D2,"mmmm d, yyyy")," at ",TEXT(E2,"hh:mm"),". Best regards, Cas Personal Finance")

Make sure you apply this formula to all the fields using the fill handle.

Expand your message to all recipients

Some messaging tools support a “merge tags” functionality that allows you to build the SMS message in the web interface. They require you to insert the column names of the Excel file in the message with special tags such as |*gender*| or |*name*|.

We receive a lot of user feedback on this functionality. It is hard to use, easy to make mistakes and you need to upload the text in exactly the correct format (ie. for gender and dates). This requires frequently re-uploading the data file and sending example messages to your own phone.

Ready to send your customers 100% personalized SMS messages?

By creating the actual message in the Excel file itself it is much easier to build your message and you can directly see the outcomes of your data merge. Although the formula might look daunting, if you break it up into parts it is actually surprisingly comprehensible. Your customers will appreciate it!

Was this article interesting?
Share it!
CM.com
connects tens of thousands of companies with millions of consumers via their mobile phone each day. Behind the scenes, from our innovative platform, CM.com makes sure companies can use these millions of messages, phone calls and payments to become part of people’s lives.

Latest Articles

whatsapp-business-blog_image-deals-offers
Sep 11, 2024 • WhatsApp

Increase Conversion With Promotional Messages on WhatsApp

In an age of mass marketing, as well as constant TV, internet and email advertising, it’s safe to say that peak sales periods like Black Friday and the Holiday Season can be something of an overwhelming experience for consumers around the world. Feeling overwhelmed by irrelevant information from companies that doesn't match their personal needs and desires, it's no surprise people might want to switch off their phones, radios, and televisions, tuning out all forms of marketing until the sales peak season is over. As an eCommerce player, you should always be looking to avoid this by diversifying and personalizing your marketing strategy in a way that suits your customers needs and sensibilities.

Unified Messaging With RCS on iOS
May 15, 2024 • Instant Messaging

Unified Messaging With RCS on iOS

RCS is a rising star among the messaging channels. It combines the reliability of the SMS network with the added benefits of rich media and other modern messaging features. Its only hiccup was reach - iOS didn't support RCS. Until now.

hero-17-sms-customer-service-templates-to-use-today
Apr 29, 2024 • SMS

17 SMS Customer Service Templates to Use Today

Using SMS customer service templates not only saves time but lets your support team create personalized, on-brand communications with just a few clicks. Today we’ll share business text message templates you can use to give your customers the information they need — fast.

SMS Security
Apr 18, 2024 • Security

Secure Your Business With SMS OTPs and Alerts

In the current digital era, technological and online advances are rapidly growing, creating new ways for businesses to engage their customers. Unfortunately, where there is growth, there will be criminals trying to steal some of the profits. Protecting business data, customer information, and online accounts is a priority for every modern business. SMS security can help protect your business and your customers from online fraud and cyber crime.

SMS Customer Service
Apr 08, 2024 • SMS

How to Effectively Use SMS for Late Payments

Organizations face a significant challenge in effectively managing overdue payments and cash flow in a positive light with customers. To collect payments while keeping relationships intact and increasing engagement, SMS has emerged as a powerful, efficient and convenient tool for both businesses and customers. In this blog, we’ll explore how to effectively use SMS for late payments, highlighting its benefits and best practices.

SMS Customer Service
Mar 11, 2024 • SMS

Optimize the Customer Experience with Valuable Insights and Direct Live Support via SMS

Add SMS to your contact center and help your agents to connect with your customers on the most reliable channel. Tackle more conversations at a time without losing that important personal touch, and gain valuable insights with streamlined SMS feedback surveys to enhance customer satisfaction rates.

SMS Appointment Notifications
Mar 05, 2024 • SMS

Streamline Your Business to Success with SMS Notifications

Appointment no-shows, delivery delays, abandoned online shopping carts, late payments - all of the above can be quite the headache for many businesses. SMS notifications offer a solution.

sms-vs-mms-vs-rcs
Feb 08, 2024 • SMS

SMS vs MMS - What is the Difference?

There are many different ways to send text messages, and the various names and acronyms may start to feel daunting. In this blog, we'll tell you everything about SMS and MMS.

sms-in-ecommerce
Feb 07, 2024 • SMS

SMS for eCommerce - Create a Successful eCommerce Strategy With SMS

Want to grow your business? Then customer contact is key. Now that everybody and everything is connected to each other via those powerful little computers in our hands, the way you communicate with customers is becoming increasingly important for your succes. Read all about how you can use SMS to elevate your customer contact, drive sales and create that loyal customer base.

Is this region a better fit for you?
Go
close icon