Google Sheets - Separate Phone Numbers

I had a first today - I was asked to prepare some data for an upload, and phone number was one of the fields.  Unfortunately, the numbers needed to be divided into area code, prefix, suffix and extension.  Ugh!  Add to that some phone numbers were all numbers, while others had periods, dashes and/or parenthesis in them.  So, I ended up using 2 methods to prepare it for a data upload. 

In this post, we will first review using the LEFT & RIGHT functions - useful functions if you ever need to select just some of an entry.  Then we will revisit using the SPLIT function, along with this application of it. 

LEFT & RIGHT functions

This was the quick way to divide the phone numbers that had no punctuation or text entered with them.  First - the functions:

  • LEFT - copies only the designated number of characters beginning on the left side of the entry
  • RIGHT - copies only the designated number of characters beginning on the right side of the entry
Both of them are of the format (data, #) where:

  • data is the data that should be used (most frequently a cell reference)
  • # is the number of characters to copy
So a couple of examples:

=LEFT (A5, 3) will result in 239
=LEFT (C7, 4) will result in 'Jenn'
=RIGHT (B1, 3) will result in 'ith'
=RIGHT (A2, 4) will result in 9324

Here are some sample phone numbers.  

For my numerical only entries, I had to use different formulas for each part of the number:
  • Area Code: =LEFT(A2, 3)
  • Prefix (combination to get middle): =RIGHT(LEFT(A2, 7), 3)
  • Suffix: =RIGHT(A2, 4) alternatively with an extension =RIGHT(LEFT(A2, 10), 4)

Filling down the formulas as appropriate (or copying the cells and pasting them in other rows) will generate the others:

SPLIT Function

I have actually shared about the SPLIT function previously. 

What's interesting is I learned a new way to use it when working on this.  Since I wanted to possibly remove multiple characters from the other entries to get them down to just numbers, I went to using a split.  But how to do that when each entry might be different?  After doing some searching, I was clued into the answer from a page on the site Extend Office

The SPLIT function is of the format

=SPLIT(data, location of split)

In the past, I've done something like =SPLIT(A5, " ") to split at the spaces.  Our new need is a bit more involved since there could be multiple possible characters breaking the data - especially dashes, periods and parenthesis.  From Extend Office, I learned you can put multiple characters in for the location of the split!

So, my formula ended up being:
=SPLIT(A5, "abcdefghijklmnopqrstuvwxyz.()- "
This would put the split at any letter, a period, a parenthesis, a dash or a space.  I did discover afterward that I even had a person use commas, so added that to the mix. 

As you can see, it divided the phone number up for me appropriately -

Filling down , then applied it to the other rows -


Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

Google Calendar - How to Share Your Calendar Via Link

Google Documents - Creating Page Anchors (aka Bookmarks)

Google Mail - Create Calendar Event that Includes Email Message

Google Contacts - Newest Area to Get Some "Google Love"