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!

Ready to send personalized SMS messages using Excel?

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

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.

how-popular-is-sms
Feb 07, 2024 • SMS

How Popular Is SMS in 2024 and What Is Its Added Value for Business?

SMS is one of the oldest text messaging services, and the rise of instant messaging apps definitely made a dent in SMS traffic. Contrary to the newer apps with their rich features, SMS is quite basic in its features- plain text messages, not fluff, and a character limit of 160. Regardless, SMS is still one of the most impactful communication channels today. Read all about SMS statistics and its continued value to modern business communication.

drive-engagement-and-convert-your-conversations-with-whatsapp-carousel
Feb 01, 2024 • WhatsApp

Drive Engagement and Convert Your Conversations With WhatsApp Carousel

Are you already talking to your customers on WhatsApp? You really should! WhatsApp Business Platform, the business communication platform of one of the most popular messaging channels in the world today, has a wide selection of powerful engagement tools to enhance customer communication and boost sales. And they've added another innovative feature under their belt: WhatsApp Carousel.

WhatsApp vs Facebook Messenger
Jan 17, 2024 • Instant Messaging

WhatsApp vs Facebook Messenger: Which Messaging Channel is Best for What?

WhatsApp and Facebook Messenger are both established messaging channels for customer engagement. But what are the differences? Which advantages does each channel have for what business use case? Let's dive into the details

WhatsApp vs SMS: A Comparison For Businesses
Nov 30, 2023 • SMS

WhatsApp vs SMS: A Comparison For Businesses

WhatsApp and SMS are both immensely popular messaging channels. Both have their own unique traits, use cases, and added business value. We probably all acknowledge this, but what are the differences between these two exactly? What are the similarities? And more importantly, which one will add the most value to your business? In this blog we'll dive into the world of WhatsApp vs SMS.

mfa-sso-blog-hero
Nov 06, 2023 • Authentication

MFA/2FA vs. SSO: Navigating the Digital Security Landscape

In today's interconnected world, the importance of robust digital security cannot be overstated. As businesses and individuals grapple with increasing cyber threats, the choice of security measures becomes crucial.

live-meta
Oct 31, 2023 • SMS

Why SMS Remains as Essential as Ever for Black Friday

One might assume that SMS has lost relevance in an era dominated by popular messaging channels such as WhatsApp and Instagram. However, when it comes to Black Friday, one of the most anticipated shopping events of the year, SMS remains as important as ever. While consumers are bombarded with emails, push notifications, and social media advertisements, the humble SMS message, with its 98% open rate, often cuts through the noise and connects businesses with eager shoppers.

black-friday-channels
Oct 31, 2023 • Messaging

Boost Black Friday Sales with WhatsApp, Instagram and Facebook

Black Friday stands out as one of the most anticipated shopping events of the year, with online sales growing 3.5% to $65.3 billion globally in 2022. With this, big and small businesses will strive for maximum exposure. Yet, this task is far from straightforward. Retailers must compete to be visible, overcome high advertising costs, and meet escalating customer expectations to cut through the noise and drive conversion. Moreover, every touchpoint in the customer journey represents a make-or-break opportunity to form relationships and drive transactions, or risk losing a consumer’s interest and trust.

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