Waffle Bytes

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 t...

(Part of my administrative assistant series on Google Sheets.) Below is a summary of all the topics covered in Session #3 of the series I have been doing for support staff.  This one covered several "intermediate" formulas available to use with Google Sheets.  We especially focused on formulas that would be useful to those school employees that would be working with large numbers of student data. Session 3 - Intermediate Formulas (November 29, 2017) Split Formula Concatenate Formula Count Formulas Hyperlink Formula

(Part of my administrative assistant series on Google Sheets.) There are some formulas that are well worth knowing when you are working with student data.  For instance, the number of times that you have a CSV download which has the name in one cell, rather than divided into one cell for the first name and one for the last name, has frustrated me and many others I know time and again.  Luckily, there is the SPLIT formula - which can take care of dividing the name up for you in quick fashion.   SPLIT Splits up the data, using an identified delimiter, putting each fragment into different consecutive cells in the row. Example: =SPLIT(A2, " ") This will use a space as a delimiter.  Here is an example of it -  The formula needs to be applied to each item you want to split - however, using the fill capability can make that quick work.   Where this is awesome is it's flexibility since you provide the delimiter.  So s...

Today I had the need to take a comma separated list from a form and create a column with the unique entries from that list.  Is it doable with Google Spreadsheets?  Most definitely! Here are the steps: 1. Use the SPLIT function to divide your entries to individual cells.  This will use as many columns as number of entries.  ( More info about SPLIT: the first parameter is the cell the data is coming from, the second parameter is what is used to divide the information.  Since I don't want the comma OR the space, my second parameter has both in the quotation marks, so: quote comma space quote .) =SPLIT(A1, ", " ) 2. Fill down as needed 3. Use the UNIQUE function to start creating your new list =UNIQUE(C1:C) 4. Add addition columns of information with curly brackets & semi-colons =UNIQUE({C1:C; D1:D; E1:E; F1:F; G1:G}) 5. Add the SORT function if you want the list alphabetized =SORT(UNIQUE({C1:C; D1:D; E1:E; F1:F; G1:G}) That...