Quickly process text using split() spreadsheet function in google docs

Posted on August 25th, 2008 in Analytics , Learn Excel , technology - 6 comments

Google docs spreadsheet app is going mainstream with each passing day. Recently they have introduced split() formula to divide text in to multiple cells using a user specified delimiter.

split-spreadsheet-function-google-docs

The syntax for this function is =split(text value, delimeter), for eg. =split("pointy haired dilbert", " ") would result in 3 individual cells with “pointy”, “haired” and “dilbert” as values. Learn more.

This is a very good replacement for excel import text functionality using which you can paste large text files in to excel sheet.


Also try:
concat() function to combine lots of cells to a single value

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

6 Responses to “Quickly process text using split() spreadsheet function in google docs”

  1. Branden says:

    This is a great feature. I wish it would have been available when I was creating Halo 3 stat pages for me and my buddies. I pulled in RSS feeds in that consisted of long text strings and had to use find(), left(), mid() and lots of if() statements to split them.

  2. Chandoo says:

    @Branden: Agree, splitting text using left(), mid(), find() is a pain and error prone. I wish they had something similar in standard spreadsheet functions of excel.

  3. Branden says:

    This isn't the only feature that's missing from Excel, I love the importfeed() and importhtml() functions as well. That's the great thing about the g-doc software, they continually improve the product and add these really great features.

    I enjoyed your work around to get the import functions into excel, so thanks for that too.

  4. So Chandoo: Any chance of sharing your Excel wisdom about the text splitting functions that /are/ available in Excel please? Maybe a full post...?

  5. tom says:

    looking for something like this?
    =LOWER(LEFT(RIGHT(C15;LEN(C15)-FIND("";LOWER(C15))-6);FIND("";LOWER(RIGHT(C15;LEN(C15)-FIND("";LOWER(C15))-6)))-1))

  6. I know you probably don't want to hear it, but it can easily be done in VBA (there is a split function and a join function) so a UDF could be written.

Leave a Reply