Skip to content

AB Google Summit – Normalizing Spreadsheet Data

March 2, 2015

2014_Alberta_Summit_logo

Spending Monday and Tuesday in Edmonton at the Expo Centre with 1,000 other folks exploring all things Google Education. This posts and others are my notes from the sessions.

Trevor Beck

Trevor’s session is all about how do you easily clean up spreadsheet data, make special views and see the data in different ways, as well as form validation so that your data comes in a lot cleaner. Trevor had some great examples, and he made it seem really easy!

https://docs.google.com/a/myghsd.ca/document/d/11vWK9_N3i4wsuDVPt41uUy2U5q8-_c23KUfvuUZz1Wo/edit

Document overview – pledge sheet

What do you do when your characters are entered as lower case? Use the function “Proper” which sets up text in correctly capitalized format. Don’t forget when you are using a Function, you start with “=”

Function “Split” to divide up text! Like first and last out of a full name. Choose what the cell is delimited by, and split it automatically. If you use “FALSE”, then it splits at the first instance of the delimiter, helpful for multiple spaces in names.

Can concatenate cells by using “&”

What if you have a space in front of the text? Can use the “Trim” function. It will remove any leading or trailing spaces.

Another example, using Postal Codes in X1X 1X1 format, can use “Left” to just pull the left three characters…

Use VLOOKUP to pull data from another sheet….

Named Ranges -> if you are constantly using the same data, you can highlight a range, and name it. Named Ranges also help when you are using fill functions, as it won’t adjust the cells you’re looking up based on each interation of the cell.

ARRAYFORMULA -> Brings over everything from a column in another sheet, as opposed to just line at a time..

SORT -> Can sort the data from a range, by a column, or multiple columns, and ascending (true) or descending (false)

Now onto Filters!! Using filters let’s you see the data that you want to see, instead of everything. However be careful when multiple people are in the same file, the views affect everyone. BUT you can also save Filter Views which makes them specific to your view.

Can even use a Filter function to build a spreadsheet just for a particular user. In this example, driver “Sam” can have his own spreadsheet which shows just the information he needs to see.

Don’t forget, you can share a document, say view only, and then you can share just the one sheet with the user. So you can lock out a sheet, or make a sheet specifically available to a person.

Wow! Trevor’s point is to remind as that even when someone gives us a sheet with bad data, we can clean it up pretty quickly and easily with various functions.

Okay, so what about validating data BEFORE it comes in? Enter Google Forms!

Built-in validation and Regular Expression

Built-in validation, specify numbers, greater than, less than, etc. Even enter custom error text. Text “must contain”. Paragraph can require a minimum or maximum character count. Checkboxes, must select at least, at most, exactly. Lots of built-in validation.

Or, you can get really smart and use Regular Expression. Samples from Trevor are pasted below:

However, there are times when you’ll need validation that doesn’t match what Google provides. Using Regular Expressions in the validation, you can create your own validations including ensuring proper capitalization. Here is a list of some useful items:

List from Trevor

Phone number (eg: xxx-xxx-xxxx)
^\d{3}-\d{3}-\d{4}$

Email address (eg. @mymacewan.ca)
^.*\b@mymacewan.ca\b.*$

or both mymacewan.ca and macewan.ca
^.*\b@mymacewan.ca\b.*|.*\b@macewan.ca\b.*$

Postal Code (eg. T5J 4S2 – capital letters, separated by space)
^\D\d\D\s\d\D\d$

Course Number (eg. ACCT101 – capital letters, no space)
^[A-Z]{4}\d{3}$

3 or 4 digits (section number)
^\d{3,4}$

Number of characters (75)
^[\s\S]{0,75}$
^[\w]{1,75}$

How cool is that? Unbelievable! Thanks Trevor! And of course if you don’t know or understand regular expressions, just Google it! Great tip!

Keep CALM and Google IT!

Advertisements

From → ATLE, GHSD

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: