Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
Why have you added the google-apps-script tag? Did on of the answers fit? –  Jacob Jan Tuinstra Jan 31 '13 at 15:08
    
@JacobJanTuinstra Because the capability can be added via Google Apps Scripting. I ended up solving my own problem by writing a 'Text To Columns' script that can now be found in the Script Gallery. Googlegooru also posted a video tutorial demonstrating its usage here googlegooru.com/text-columns-google-spreadsheets. –  Evan Plaice Jan 31 '13 at 18:16
    
What's the difference to the solution already provided by Google Spreadsheet: SPLIT. That the values are set? You can paste values you know. –  Jacob Jan Tuinstra Feb 1 '13 at 9:19
    
@JacobJanTuinstra In your first solution try copying B2 to B3. The result cells contain continuation formulas, not raw data. The cells outputted from the script contain the actual raw data so they can be copied/moved without any issues. The intent of this question is to find an equivalent to Excel's 'Text to Columns' feature. Until, Google officially adds support the script is as close as it's going to get. –  Evan Plaice Feb 1 '13 at 18:53
add comment

5 Answers

up vote 12 down vote accepted

Leverage Google Apps Scripting to extend the UI

Update: This script is available in the Script Gallery. Just search for 'Text to Columns', install the script, and refresh the spreadsheet. Note: it may be slow to load the first time.

Text to Columns, is a very handy feature and one of the reasons that a lot of Google Spreadsheet users go back to using Excel. Until, Google decides to officially support the feature, this solution can be used as a polyfill to add in the functionality.

Here's the code:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({ name:"Text to columns", functionName:"textToColumns" });
  menuEntries.push({ name:"Text to columns (custom separator)", functionName:"textToColumnsCustom" });
  menuEntries.push(null);
  menuEntries.push({ name:"Columns to Text", functionName:"columnsToText" });
  menuEntries.push({ name:"Columns to Text (custom separator)", functionName:"columnsToTextCustom" });
  ss.addMenu("Advanced", menuEntries);
}

function textToColumnsCustom() {
  var separator = Browser.inputBox("Text to column","Enter the the separator",Browser.Buttons.OK);
  textToColumns(separator);
}

function columnsToTextCustom() {
  var separator = Browser.inputBox("Column to text","Enter the the separator",Browser.Buttons.OK);
  columnsToText(separator);
}

// Expands a single cell of CSV formatted text to multiple columns
function textToColumns(separator) {
  var sep = typeof(separator) !== 'undefined' ? separator : ',';
  var ss = SpreadsheetApp.getActiveSheet(); 
  var r = ss.getActiveRange();
  // check that only one column was selected
  var col = r.getColumn(); 
  if(col !== r.getLastColumn()) {
    Browser.msgBox("Error", "Invalid selection, too many columns.", Browser.Buttons.OK);
    return;
  }  
  var firstRow = r.getRow();
  // short cut the one row selection
  if(firstRow === r.getLastRow()) {
    var values = r.getValues().toString().split(sep);
    ss.getRange(firstRow,col+1,1,values.length).setValues(new Array(values));
    return;
  } else {
    var rows = r.getValues();
    var values = [];
    var cols = 0;
    for(var i = 0, len = rows.length; i < len; i++) {
      var rowValues = rows[i].toString().split(sep); 
      var rowValuesLen = rowValues.length;
      if(cols < rowValuesLen) { cols = rowValuesLen; }
      values.push(rowValues);
    }
    // set all values at once (padding required because setValues doesn't accept jagged 2d arrays)
    padRow(values, cols);
    ss.getRange(firstRow,col+1,values.length,cols).setValues(values);
  }
}

// Pads a row with empty values to the specified length
function padRow(array, length) {
  for(var i = 0; i < array.length; i++) {
    var arrLen = array[i].length;
    if(arrLen < length) {
      var padLen = length - arrLen;
      var padding = new Array(padLen);
      array[i].push.apply(array[i], padding);
      for(var j = 0, len = array[i].length; j < len; j++) {
        if(typeof(array[i][j]) === 'undefined') {
          array[i][j] = "";
        }
      }
    }
  }
  return array;
}

function columnsToText(separator) {
  var sep = typeof(separator) !== 'undefined' ? separator : ',';
  var ss = SpreadsheetApp.getActiveSheet(); 
  var r = ss.getActiveRange();
  var col = r.getColumn();  
  var firstRow = r.getRow();
  var rows = r.getValues();
  var values = [];
  for(var i = 0, len = rows.length; i < len; i++) {
    var value = rows[i].join(sep);
    values[i] = [value];
  }
  col -= 1;
  ss.getRange(firstRow,col,values.length,1).setValues(values);
}

Save and close the script editor. Then, refresh the spreadsheet. It'll take a second to load but you should see a menu called 'Advanced' popup after 'Help' in the toolbar.

Usage:

  • Select the cell(s) containing the values to split
  • Select AdvancedText to columns

That's it. You can also do a split using a custom delimiter (via 'Text to Columns (Custom)') and reverse the process (via 'Columns to Text').

share|improve this answer
    
I just installed this from the Scripts Gallery and it wasn't working. I fixed it by going to Tools → Script Manager → Edit button and adding a semi-colon after each function definition's closing brace. Thanks for the script. –  bob esponja Feb 8 '13 at 11:19
    
@bobesponja Thanks for the heads-up. I know there's a bug with Google Scripts where event triggers from imported scripts don't register properly. To fix it, just add the onOpen trigger manually. –  Evan Plaice Feb 8 '13 at 17:11
add comment

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.

share|improve this answer
add comment

I used the split function, and it worked perfectly, since it uses the continue formula as well and it does exactly the translation from text on column A to columns BCDE with proper spacing.

My example:

Cell A1= text1, text2, date1, number1
Cell B1= split(A1,",")

Outcome in B1 is text1. Outcome in C1 is text2 Outcome in D1 is date1 Outcome in E1 is number1.

It takes care of the format, since the date was spelled as 1-jun, and it translated to 01/06.

The formulas that split used in cells BCDE where CONTINUE(B1; 1; 2), CONTINUE(B1; 1; 3), CONTINUE(B1; 1; 4). All of this part was created automatically.

share|improve this answer
2  
Is this a comment or a solution? –  Jacob Jan Tuinstra Aug 28 '13 at 17:22
add comment

Furthermore, after using the SPLIT function which will provide the array containing the separated values, you can isolate a specific row or column from this array with the INDEX function:

=index(split(importXML("https://www.google.com/search?q=stackexchange","//div[@id='resultStats']/text()")," "),1,2,1)

Can be useful for retrieving the number of results for a Google query for example

share|improve this answer
add comment

And an equivalent of columnToText would be to use the =JOIN(delim, array) formula. For example, =JOIN(",", A1:A10) would result it concatenated string of values from cells A1 thru A10.

share|improve this answer
1  
That's true, but the OP said it had values (comma separated) in one cell. –  Jacob Jan Tuinstra Sep 8 '13 at 12:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.