Growth5 Blog

Thursday, April 29, 2010

Google Docs: Two New Powerful Functions


Yes, we are Google Docs fans at the Five Group. Why?

  • It's a great place to organize files that our internal and external teams have access to from anywhere they have an internet connection;
  • multiple people can view and work on these files at the same time in real-time; and
  • the functionality for each type of Google Doc is getting more powerful all the time.
Google has developed Docs really well.

We use a third party online app that helps us run our businesses day-to-day, but for almost everything else we use Google Docs. We probably get the most use out of the Spreadsheets, certainly with all of the financial docs for our service businesses and our portfolio companies. However, we probably use the Spreadsheets app equally for non-financial documents because it's excellent for organizing information (agendas for meetings with real-time notes, timelines, inventory tracking, etc...)

The "Microsoft Word-like" Google Doc app called Document is perfect for working on text with multiple people, especially if they can't be in the same room. Awhile back we were working (last minute of course) on a presentation one of our portfolio companies was giving in Switzerland that day. The COO of the portfolio company was in Switzerland at his hotel, I was racing down I-95 somewhere in NJ (connected to the web via my Mifi in the passenger's seat) and Grace was at the office in Baltimore – all of us on Google Docs watching Grace make edits to the changes we were talking through.

Google Docs eliminates the need for the every 30 seconds questions, "can you read it back again please? What did we say again?" We could see the presentation as the edits were being made. The Swiss heard a much better presentation than would have otherwise been given that day.

Google Docs has introduced two new functions/formulas you should know about:
1. IMPORTRANGE() can reference data from any of your Google Doc spreadsheets. We've covered the functionality of scraping the web for data you can pull into your Google Doc spreadsheet here and part 2 here. Technically, you could pull data from your other Google Doc spreadsheets using these original functions, but it was clunky. This new function makes it easier for you to pull information from any of your Google Docs spreadsheets to each other. Very helpful.

2. SPLIT() cleans up text in a cell by splitting it into multiple cells at any delimiter you choose. This function makes me smile because it is now my answer to this perpetual question... "I have 3,000 names in a list in Excel, their full names are in one cell each, how do I separate out the middle names without having to edit the cells manually?"

My answer used to be, "please don't ask me to explain how this works, but try using this formula by changing the A2 wherever it appears to the cell where your name list starts."

=TRIM(IF(ISERROR(FIND(",",A2,1)),A2,MID(A2,FIND(",",A2,1)+1,
IF(ISERROR(FIND(" ",A2,FIND(",",A2,1)+2)),LEN(A2),
FIND(" ",A2,FIND(",",A2,1)+2))-FIND(",",A2,1))))

Now my answer is, "just upload the excel file into Google Docs, and use SPLIT()."
Here's a quick and simple example of how you could use SPLIT().

You have a list of names that starts at cell A2 – the names are in one cell each and appear as follows:

Last Name, First Middle

You would put the formula =SPLIT(A2,",") into cell B2. A2 is the cell to consider, and the "," is the delimiter. As you can see in the screen capture below, the last name appears in cell B2, whatever is after the comma appears in cell C2.



If you would like to split the first and middle name in cell C2, you would enter the formula =SPLIT(C2," ") into cell D2. C2 is the cell to consider, the " " (space) is the delimiter. As a result, the first name appears in cell D2, the middle name in E2.



You still may have some clean up work to do after using SPLIT(), but it's simple and efficient and a great way to start a project like the name list described above.

If you have questions about either of these functions, comment below or click the "Contact Us" link at the top right of this page and we'll do our best to respond ASAP.

Labels: ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home