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.) Number entries can be easily formatted within Google Sheets.  In fact, if you know how you want the entries formatted prior to entering the data, you can even pre-format the cells so that the formatting occurs as you enter data! Options on the Formatting Toolbar : The most common formatting options are found right on the Formatting Toolbar - Here you can easily change a number to currency or a percentage, establish the number of decimals desired, and access the formatting menu. Menus for Formatting : There are actually two locations you can see the menu options for formatting - under the Format Menu and selecting Number, in addition to the formatting toolbar when clicking the "123" icon - The menu has many of the most popular formats, but notice at the bottom there is even a choice for More Formats where you can explore additional currency and date/time formats, or even create a custom format.   ...

Another foray into creating a script to help make a process more efficient. We had a program that spent a LOT of time with the invoicing process.  They were hoping that they could use a Google form to gather information for invoices, then use the Add-on Autocrat to create the invoice which could then be shared with appropriate parties. The problem was that each invoice needed to be assigned a unique number.  Enter a script to add to the spreadsheet.  After creating it, I realized that this simple script could be used in a variety of ways so thought I'd share (I've already used it for two other applications!). 1. Create your form to collect the data (not covering this here - I am assuming you know how to create a Google Form) 2. In the result page, select to create spreadsheet so you can view the data in a spreadsheet 3. Go to the Tools Menu and open the Script editor 4. Delete out the default text Replace with this script (all in highlighted gray including...

Today I discovered something quite by accident, but it is a hack worth knowing. There have been many times when working in a Google Spreadsheet that I have dealt with numbers and dates that I want to display as I type them .  I don't want it to auto-format the date to standard, nor drop the 0 on the front end of a number that needs it to display.  There is a HACK to do this!  Did you know that??  I didn't - and I read a LOT of Google blogs, and follow a lot of Google support groups.  So I figure it must be worth sharing. Here's the hack - add an apostrophe to the front of it.  Yup, that's it. If you type '09264 into the cell, this is what you get: No apostrophe shows, just the number.  No more "lost" zero! It works well with dates you want in a format that is not an option.  Here's one that shows you how my March 3 displays in the cell without the apostrophe (cell A5) - it's converted to 3/3/2016.  But typing in 'March 3 (as I di...