Raise your hand if you’ve ever participated in a phone bank. Ok, now raise your hand if you’ve compiled the call list for a phone bank. It’s not exactly a piece of cake, right? First, you must wrestle with your database and come up with a query for the right contacts who have either donated recently (or not), come to an action/event recently (or not) or meet some other type of special criteria. Wait, where did I save that query last time I had to do this??? Aw man, now we have a new contact tagging feature, but we’re only part way through updating data, and now I’m really in a pickle. What about all of those people who hate phonecalls? Gotta make sure to exclude them! Then, you have to wrangle your phonebanking team and logistics. No small feat. Phonebanking is a bit like pulling teeth sometimes. As if that’s not bad enough, finally, you have to format all of the call sheets so that they are legible. *GROAN*
Wouldn’t it be nice if your phone numbers could be properly formatted as soon as you opened your Excel or Google Drive doc? In my experience, it is SO MUCH EASIER to dial numbers when I only have to remember 3-3-4 digits at a time. So without further ado, we’re going to talk about a few different options for formatting phone numbers so that they go from…
1234567890 –> (123) 456-7890.
Wear a mask!!!
I was recently working with a client to update a *slew* of volunteer sign-up forms, most of which required phone number data entry. I learned that there was a feature in the form platform that I was using called “mask” … which let me input a number format and automatically updated all data input in that field to the appropriate format.
As I was doing some further research for this blog post, I learned that “masking” data is often considered akin to “encrypting” it… basically, jumbling the letters for security purposes. But what I’m talking about here is masking in format only and it looks like there’s a precedent for doing so in various programs. Some people call it “input mask” if you want to learn more.
For example, I found some interesting instructions here for Microsoft Access (although I don’t know anyone who’s using Access personally!), instructions here for CSS (which I have no idea how to use, but I think it’s widely applied!), and instructions here for Form Assembly, which is the form-builder that I referenced above. I would say that all of these fall under the general category of prevention – if your phone numbers are entered in a “proper” format, then you won’t have to re-format them later! But if you’ve gotten this far, you probably already have some sloppy phone numbers on your hands, so let’s keep moving and figure out how to deal with them.
special format in excel
Did you know that Excel already has a custom “number type” for phone numbers? Well, today is your lucky day! They explain all of the ins and outs here. Follow these simple instructions and you can be well on your way!
- Select the cell(s) or column(s) that have phone numbers
- In the “Numbers” section of the menu/tool bar, (where is usually says “General” in a drop down menu), scroll down “More Number Formats.”) Or, click the teeny-tiny “expand” icon in the bottom, right corner of the menu tab. (It looks like a box with an arrow).
- Select “Special” from the menu. Your options will be “Zip Code,” “Zip Code + 4”, “Phone Number,” and “Social Security Number.”
- Select “Phone Number” and then “Okay.”
TIP: In general, I don’t recommend using the SSN option because you shouldn’t type SSN’s into a spreadsheet if you can avoid it. However, I always recommend using the Zip Code option because then you can deal with those pesky NJ zips that start with zero!
special format in Google Sheets
Lucky for us, G-Sheets has a comparable feature!
- Select the cell(s) or column(s) that have phone numbers
- From the top menu bar, hover over “Format,” then “Number” and then select the last option.
Looking to further specialize? I did find this extension that seems pretty cool, and worth checking out if you are a heavy Sheets user with consistent, irksome phone number formatting issues. I found this tutorial helpful, especially if you are looking for resources that deal with international phone numbers. However, it uses an unusual (and in my opinion, difficult!) google sheets formula called “REGEX replace” (see some more examples here). Perhaps a subject for a future blog post?
starting fresh
In some circumstances, your phone number data might just be TOO FAR GONE to be able to apply one of these “special number formats” to fix it for you in a jiffy. These formats work if your numbers are “clean” (ie… 1234567890) but they don’t work if there are random spaces, periods, dashes, etc (ie 111.222.3333). It’s kind of like taking off the wallpaper before you paint. *Groans*
Luckily, we can use formulas to make it MUCH less time consuming and painful to do this awful task ;). I think that’s what our friends at Excel Jet were getting at in the initial screenshot of this blog post. Follow along with these steps if you want to give it a try!
The trainer does a good job showing how to link multiple formulas together, even using linebreaks to make the line-up more readable. However, I don’t find that the easiest way to learn or find mistakes. My advice is to remove one symbol at a time so that you can make sure that formula is working before adding another. Who cares how many columns it takes to get your “numbers only” phone number? No one’s counting!
bonus round
Sometimes, we have to clean up phone number data not only for phonebanking, but also for importing into another system (like a database, or a text-sending-platform). Those systems might have their OWN number formatting requirements. We can use some of the same skills that we reviewed in this blog post to format numbers into a variety of different, well, formats!
bone phanking
Ok, so back to our original goal: a nice spreadsheet that we can rely on for easy phone banking – something pretty much all activists need to do every now and again. I think you can use the tools and resources here to get started right away! Whether you are cleaning up data from a sign-up form, exporting from another system, uploading into a different system, or just trying to make heads/tails of phone numbers, you are in good company and we are in it together!
For the record (if your database doesn’t have my preferences stored…. kidding, I promise!), I prefer blog comments, emails, and texts to a cold call any day of the week. So you can cross me off your list, and keep moving to your constituents who can’t wait to hear from you!