Google Sheets - Separate Phone Numbers

By -
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

By -
Image
(Updated on 3/21/2017 - triggers have been moved!) ( I just created a new related post on how to create a multiple choice  question on the form that a bit simplified - takes the "other" answers in a multiple choice question on a form, and updates the multiple choice options with those as options.) I guess it was only a matter of time before I ended up dabbling with Google Scripts.  Today I was asked by a teacher how he could populate a drop-down list on his Google Form from data on a spreadsheet - and have it update as he changed the data.  Not finding an add-on for this (why recreate the wheel? :-)  I explored creating a script.  And...discovered that this task is not too hard! To do this yourself - 1. Create your form.   Really important step.  Make sure to create your drop-down item and give it a title, but you do not need to add any options to it (because later you will populate it from the spreadsheet). 2. Create the spreadsheet where your list will reside. Yo
Read more

Google Calendar - How to Share Your Calendar Via Link

By -
Image
Google Calendar provides a couple ways you can share your calendar.  The first way you will see below provides a link that lets another person subscribe to your calendar.  They will need to be logged into their Google account, and it will display YOUR calendar along with their own. (Personally, I find this method the best for adding my kids' and husband's events so I see our families events all in one calendar.)  The second method displays your calendar in a browser window.  This is probably the preferred Teacher method for sharing with parents. (Here is a video on How to Share Your Calendar Via a Link .) When in your Google Calendar: Click on the Options menu (stacked dots) for the calendar you want to share - Select Settings and sharing - Click on Access permissions on the left side - Click on the Get shareable link - Note that who can see the calendar with this link depends on the access permissions you have set right above.  For example, with the setti
Read more

2019 MACUL Conference Tipsheet #1 - Food Tips & Special Events

By -
Image
(This is 1 in a series of 4 to help you get ready and excited about going to this year's conference.) Yes, you are attending the conference for all the great learning and networking.  However, eating is still a necessity and it is important to know where to find food while in Detroit...especially food that you can get quickly, without waiting in long lines!  Here are some tips and hints for dealing with that while at Cobo, along with some special events you should know about: Staying Hydrated : make sure you stay hydrated! there are several fountains and water coolers provided throughout the venue to get water - bring along a refillable cup/mug and keep filling it all day instead of purchasing expensive bottles of water Food at Cobo : there are several food vendors right in COBO; some tips about these: there will be around 4000 attendees AND hundreds of additional vendors at the venue, and everyone will be taking about the same meal breaks - either pick up food at
Read more

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

By Anonymous -
Image
Be ready!  Your Google Contacts is about to update its looks.  I don't know about you, but I don't go to Google Contacts often.  I just don't like it.  If you need a refresher, this is what it looks (looked) like - Looked & felt very...dated.  This morning in going to one of my Google accounts, I got the pop-up window in my contacts.  Here is a screenshot of the new look - Hoping that the Google Love includes more than just the interface.  If there are any big changes that make Contacts more easily managed & navigated, you can be sure to see another blog post or two about it!
Read more

Google Slides - Rulers & Guides

By -
Image
A menu change happened for me this morning in Google Slides (on just my work account, not on my personal account yet).  The addition of both turning on/off a ruler, and adding guides onto the slide to help with placement if you are varying from the pre-built theme layouts. Here is the menu change showing the additional options - RULER The ruler works as you would expect it to.  When you select it from the View menu, it displays a horizontal and vertical ruler at the top and left of the side - When you are moving an object on the slide, you will see blue lines marking the location of the ends and middle of the object on both toolbars - GUIDES When you select to view the guides, you see the midpoint guides on the slides -  Guides do have some more flexibility as you can add additional guides on the slide, and drag them to the location of your choice - There is also the option to "Edit guides" and enter them in a window, as well -
Read more