Tuesday, July 17, 2012

yogi_Parse Response Table Data From Within One Cell

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #633   Jul 17, 2012     www.energyefficientbuild.com.


user ThatSQLGuy said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/nOThkmn8-04)
Parsing table data from within 1 cell (Similar to Excel's Text to Columns data conversion)
I have created a form whereby users will submit data for two fields.  Field 1 is there name, Field 2 is a "table of data".  This part works fine and the data loads on to Sheet1 associated with the form.
Now I would like to add a second sheet to convert the "table of data" into a standalone sheet where the first column is the Field 1 data from Sheet1 and the remaining columns are the columns in the table of data that are contained in field 2.
Example:
Assuming resulting form data looks as follows:

Field 1       Field 2
ThatSQLGuy    "Home Address" "123 Main St" "Seattle" "WA" "98188"
ThatSQLGuy    "Work Address"  "123 abc avenue" "Bellevue" "WA" "98004"

I would expect the data conversion page to show the following

Field 1       Field 2            Field 3              Field 4             Field 5   Field 6
ThatSQLGuy    Home Address       123 Main St          Seattle             WA        98188
ThatSQLGuy    Work Address       123 abc avenue       Bellevue            WA        98004

Now Ideally I would like the form to do this automatically if the address data was just pasted into the paragraph field on the form, but I can settle for a data conversion on a separate sheet as a work around.
I thought maybe ImportRange might work but I cannot seem to figure out how this function for this purpose.
Any help would be appreciated.
ThatSQLGuy
---------------------------------------------------------------
following is a solution to the problem