|How do I use "Text to Column" feature in Spreadsheet?||bwclifton||1/28/09 12:48 AM|
I am using Spreadsheet to work with a group, and we are constantly needing to use the Excel "text to column" tool. Currently we have to export to Excel and use the feature in there rather than do it in Docs. Does anyone know if Docs has that capability to do this for us, or have any suggestions on how to make it easier? Thanks in advance.
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||1/28/09 1:03 AM|
You may want to use the SPLIT function combined with an ARRAYFORMULA. Because of a problem with he split function (the first value in a range will not split correctly) you may have to insert a blank row on which to put the formula
A2: name, address, city
Above data needs to be split by comma.
Now insert into B1 (i.e. one row above the data, because of the split bug):
B1: =ARRAYFORMULA( IFERROR(SPLIT( SUBSTITUTE(A1:A ; ","; ", ") ; "," )))
This will split up the data into correct columns.
Note the SUBSTITUTE ( A1:A ; "," ; ", " ) - i.e. substitute comma by comma+space is necessary because split ignores empty fields in its output and shifts results to the left; the added space counters that.
|Re: How do I use "Text to Column" feature in Spreadsheet?||taxisteco||2/6/09 11:12 AM|
This is ridiculously difficult in comparison to Excel. I know Google Docs is free, but...
Isn't there something easier?
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||2/6/09 11:25 AM|
I would have given you an easier option if it existed... And the method above isn't rediculously difficult, it's a workaround for a missing feature. You just determine what is easier to do, doing this in a Google Docs spreadsheet or exporting to Excel.
Note if you use an extra sheet exporting the result of the split as .csv can even be easier.
Just let the formula reference the first sheet e.g. Sheet1 and put the following formula into A1 of the new sheet:
A1: =ARRAYFORMULA( IFERROR(SPLIT( SUBSTITUTE('Sheet1'!A1:A ; ","; ", ") ; "," )))
Now you can export the second sheet as .csv.
Just try it once, it's really not that hard...
|Re: How do I use "Text to Column" feature in Spreadsheet?||virus||5/14/09 12:48 PM|
you seem to be a pro at this.
1. does "ARRAYFORMULA" apply to other things? (ie. I'm not having a prob w/ the first value)
2. I want to take a column of imported comma-delineated data, and split it into the right # of columns (1 for ea field).
right now, I am doing 1 row at a time w/ split function. I have 1000's to go!
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||5/14/09 1:15 PM|
@1 ARRAYFORMULA is used to allow array expressions like the one in the formula which works for the entire coloumn. The problem I showed the work around for only is visible when using SPLIT in an array expression with ARRAYFORMULA.
@2 Try the method I described. Read the instructions carefully. Then test it out on a small scale. then apply it to your 1000 rows
|Re: How do I use "Text to Column" feature in Spreadsheet?||virus||5/14/09 1:48 PM|
I don't understand your instructions well enough.
Does ",,," just shorthand for name & address in this case?
B1: =ARRAYFORMULA( IFERROR(SPLIT( SUBSTITUTE(A1:A ; ","; ", ") ; "," )))
Why did you use semicolons here, when there were commas used in A,2,3,4?
I have data in a column (A3:A999), ea field has data LIKE this: 1981,01,23,215441.60,-29.68, 60.84,7.0, 10 (but ea 1 is different, of course)
It needs to be split, so that A3 becomes A3:J3
If I have to enter ALL the data in the formula, and/or EVERY field that it will go in, I can't see that that will have less steps than just using split function, 1 at a time. (I can do it in 10 strokes/clicks per line right now)
Am I still not getting it?
[BTW, I am getting into a column first by just copy/paste.]
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||5/14/09 4:23 PM|
Please see the spreadsheet referenced below. The formula - =ARRAYFORMULA( IFERROR(SPLIT( SUBSTITUTE(A1:A ; ","; ", ") ; "," ))) - is in cell B1 and this single formula automatically will split any comma delimited data in any cell in column A (except row 1, because of a bug in the SPLIT function).
The result of the split will be in the adjacent cells starting in column B (as the formula can not replace the data in column A).
The post-processing needed would be to copy the data in the columns B...I and paste them in e.g. the columns K...R using Edit->Paste value only . After checking columns K..R now contain the split up data (not references!) the columns A...J can be totally deleted from the spreadsheet. And you have the data as you wanted it.
|Re: How do I use "Text to Column" feature in Spreadsheet?||virus||5/14/09 5:53 PM|
Well, I can't say that I understand exactly how that works, but it works.
You don't have to be an electrician to know how to turn on the lights. My lights are on now!! Thanks a million!
|Re: How do I use "Text to Column" feature in Spreadsheet?||boby_k||2/17/10 3:40 AM|
This is first in my wish list. Google Docs should have TEXT to Columns on the menu.
|Re: How do I use "Text to Column" feature in Spreadsheet?||Bukov||3/23/10 9:52 PM|
I'm also stunned. The electrician analogy is very apt (although damned if this wont make me look real close at the formula until I figure it out). Either way, it was like waving a magic wand, did *exactly* what I wanted. Ahab you're a genius, thanks tons man!
|Re: How do I use "Text to Column" feature in Spreadsheet?||iServio||4/10/10 4:31 AM|
I came here to find a solution also.
My data looked like this:
One more problem for me was the dot(.) instead of a comma(,), because my setting for french is a comma. What I ended up doing is copying the data to notepad++, doing a search and replace from . to , and using the ALT key to copy one column at a time to google spreadsheet.
Was faster than playing around with formula. But it would be nice to have a tool included in google to do that, similar to excel.
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||4/10/10 5:21 AM|
You may have used Edit->Find and Replace directly in the spreadsheet...
|Re: How do I use "Text to Column" feature in Spreadsheet?||cecil.huang||4/21/10 9:45 AM|
It is *very* helpful to know about the bug in SPLIT when applied to arrays. Thanks Ahab for the tip and the workaround. My follow up question is how to delete the first (i.e, "dummy") row of the resulting 2-D array. This would be helpful, if, for example, I want to post-process the 2-D result (i.e., transpose it, etc.).
|Re: How do I use "Text to Column" feature in Spreadsheet?||cecil.huang||4/21/10 10:07 AM|
Here is an example that you can all view/edit:
Basically, I am trying to rearrange some data exported from a popular financial software program (YNAB) into a 2-dimensional format. The intention should be obvious once you look at the spreadsheet. I've made use of various automatically-resizing array techniques that I've read elsewhere in the forum.
Now knowing about the SPLIT bug described above, I am able to use a single formula to arrive at my desired result (formula in cell O7). Just about, anyways. What I really want is to delete the dummy 1st row from the result of O7 and transpose the rest of the matrix. The desired result you can see in S7, but I am unable to collapse this computation into a single formula. (The desired result is desired, as illustrated in Sheet2, where I'd like the result to populate in B2.)
Help? Ahab? Octavio? Yogia? APL? Anyone else? You've all been great, I appreciate it.
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||4/21/10 10:48 AM|
|Re: How do I use "Text to Column" feature in Spreadsheet?||cecil.huang||4/21/10 5:55 PM|
Thanks for introducing me to VMerge. Seems like a lot of code can be simplified by using this GAS custom function.
What I really want is some form of VMerge that can accept an automatically expanding list of arrays. The ARRAYFORMULA/SPLIT/CONCATENATE mechanism that I used (inspired by your previous posts) accomplishes this. Specifically, referring to the spreadsheet Test.Expenditures above, I want the spreadsheet to recalculate and resize the desired result automatically, even when the data in columns A-C changes as far as the # of months and the # of categories (the invariant being the ordering scheme -- with respect to columns A & B -- of the data elements in listed column C).
Any ideas? Thanks so much for your interest and expertise.Cecil
|Re: How do I use "Text to Column" feature in Spreadsheet?||cecil.huang||4/23/10 10:09 AM|
Here's my best shot at the problem. Please refer to the above workbook, under the sheet name "RawData-CecilNew". The following formula does everyone I want, except remove the first line:
ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(CONCATENATE(" ^";REPT(TRANSPOSE(INDIRECT(B4));MOD(ROW(F7:F9),F2) = TRANSPOSE(MOD(ROW(INDIRECT(B4)),F2))) & TRANSPOSE(IF(ROW(INDIRECT(B4)) = MAX(ROW(INDIRECT(B4))); "^"; " "))),"^"))," "))
|Re: How do I use "Text to Column" feature in Spreadsheet?||mattcohen||6/22/10 1:23 PM|
How would i need to adjust the formula for space-separated data: ie Title FirstName Last Name into separate columns?
|Re: How do I use "Text to Column" feature in Spreadsheet?||ahab||6/22/10 1:33 PM|
With the names in column A from row 2 down, put in row 1 in cell B1 the formula:
B1: =ARRAYFORMULA( IFERROR(SPLIT( A1:A ; " " )))
|Re: How do I use "Text to Column" feature in Spreadsheet?||llricci||7/18/10 2:46 PM|
I have been successful with a very simple method. I copy and paste the information into Word, then copy and paste that result into the spreadsheet. It works every time for me. Hope that helps.