Google-sheets – Text to columns conversion in Google Spreadsheets

google sheetsgoogle-apps-script

How do you do a text-to-columns conversion in Google Spreadsheets?

For example, I have the following data string in one cell: 

5,233,6,2,6,7,2,2,6,6

I want to break it apart by the comma separator into columns.

Edit: I changed the accepted answer to one that doesn't use Google Apps Scripting because Google seems hell-bent on nerfing it's ability.

Best Answer

The follow formula will do just that; text-to-column:

A1=5,233,6,2,6,7,2,2,6,6
A2=SPLIT(A1;",")

And the next; text-to-row:

A1=5,233,6,2,6,7,2,2,6,6
A2=TRANSPOSE(SPLIT(A1;","))

UPDATE 03-02-2013
If you split the result of A1 and paste the values, it will give the same result as all the lines of code used in the OP's answer. I gave it a shot at it as well with Google Apps Script and this is what I created: text to column

function mySplit() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sh.getActiveCell().getValues()[0];
  var sCell = cell[0].split(",");
  var row = sh.getActiveCell().getRowIndex();
  var col = sh.getActiveCell().getColumnIndex();

  sh.getRange(row,col+1,1,sCell.length).setValues([sCell]);  
}

I simple use the build-in split function to split the result and add it it to the sheet, nothing more and nothing less.

Related Topic