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.
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
6 Responses to “Quickly process text using split() spreadsheet function in google docs”
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.
@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.
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.
So Chandoo: Any chance of sharing your Excel wisdom about the text splitting functions that /are/ available in Excel please? Maybe a full post...?
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))
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.