您如何在Google Spreadsheets中进行文本到列的转换?

例如,我在一个单元格中具有以下数据字符串:

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


我想用逗号分隔符将其分成几列。能力。

评论

为什么要添加google-apps-script标签?答案是否合适?

@JacobJanTuinstra因为可以通过Google Apps脚本添加此功能。最后,我编写了一个“文本转列”脚本来解决自己的问题,该脚本现在可以在“脚本库”中找到。 Googlegooru还在googlegooru.com/text-columns-google-spreadsheets上发布了视频教程,演示了其用法。

Google Spreadsheet已提供的解决方案与SPLIT有什么不同?设置值了吗?您可以粘贴您知道的值。

@JacobJanTuinstra在您的第一个解决方案中,尝试将B2复制到B3。结果单元格包含连续公式,而不是原始数据。从脚本输出的单元格包含实际的原始数据,因此可以进行复制/移动而不会出现任何问题。这个问题的目的是找到与Excel的“文本到列”功能等效的功能。直到Google正式增加对脚本的支持为止。

@Rubén非常稳定。我之前检查过,给人的印象是脚本完全消失了。原来,他们只是杀死了脚本库。我将采用您建议的答案,因为它是最简单的可脚本化解决方案。感谢您的反馈。

#1 楼

以下公式可以做到这一点;文字转栏:

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


下一个; text-to-row:

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


UPDATE 03-02-2013
如果拆分A1的结果并粘贴值,将得到相同的结果结果是OP答案中使用的所有代码行。我也使用Google Apps脚本对此进行了测试,这就是我创建的:文本到列

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]);  
}


我简单地使用内置拆分功能来拆分结果并将其添加到工作表中,仅此而已。

评论


很好...在不扩展UI的情况下如何称呼它。您可以添加一个演示用法的简单示例吗?

–伊文·普莱斯
2015年5月6日19:10

#2 楼

利用Google Apps脚本将UI

文本扩展到列,这是非常方便的功能,也是许多Google Spreadsheet用户重新使用Excel的原因之一。在Google决定正式支持该功能之前,此解决方案可以用作polyfill来添加功能。

代码如下:

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);
}


保存并关闭脚本编辑器。然后,刷新电子表格。加载需要一秒钟,但是您会在工具栏中的“帮助”之后看到一个名为“高级”的菜单。

用法:


选择包含要拆分的值的单元格
选择“高级”→“文本到列”


就是这样。您还可以使用自定义定界符(通过“从文本到列(自定义)”)进行拆分,并逆转过程(通过“从列到文本”)。

评论


我刚刚从Scripts Gallery安装了它,但无法正常工作。我通过转到工具→脚本管理器→编辑按钮并在每个函数定义的右括号后添加分号来修复它。感谢您的脚本。

– bob esponja
13年2月8日在11:19



@bobesponja感谢您的注意。我知道Google脚本存在一个错误,其中导入的脚本的事件触发器未正确注册。要解决此问题,只需手动添加onOpen触发器。

–伊文·普莱斯
13年2月8日在17:11

我在“脚本库”中没有看到它。还在吗?

–艾伦·斯佩特斯(Ellen Spertus)
2014年9月12日19:04

@espertus看起来Google摆脱了脚本库,转而使用他们的新附加组件。只需将上面的代码复制到脚本中,关闭,然后重新打开文档即可。

–伊文·普莱斯
2014年12月16日21:11

在某些情况下,这是行不通的。 1,421,873,190,017,370,000,000,000 1.42E + 24该csv行本应拆分为9列,但仅拆分为1。

–haventchecked
15年7月25日在18:05



#3 楼

我使用了split函数,并且效果很好,因为它也使用了continue公式,并且可以准确地将A列上的文本转换为BCDE列并具有适当的间距。

我的示例:

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


B1中的结果是text1。
C1中的结果是text2
D1中的结果是date1
E1中的结果是number1。 br />
它会保留格式,因为日期被拼写为1-jun,并转换为01/06。 ,CONTINUE(B1; 1; 2)CONTINUE(B1; 1; 3)。所有这部分都是自动创建的。

评论


这是评论还是解决方案?

–雅各布·扬·图恩斯特拉(Jacob Jan Tuinstra)
13年8月28日在17:22

#4 楼

将您的csv数据转换为tsv(制表符分隔的值)。
然后将其粘贴。

评论


我只是尝试用常规粘贴,但没有用。可以使用浏览器的“粘贴和匹配样式”进行粘贴(Chrome / MacOS)

– nhed
15年11月10日在16:48

一个简单的ctrl + v对我来说非常完美。到目前为止,这似乎是最简单的解决方案!

–Didier L
16年1月11日15:28



#5 楼

我非常喜欢Evan使用Apps Script的答案,并做了一个小改进:添加了对正则表达式定界符匹配的支持。我在onOpen的menuEntry中添加了:

menuEntries.push({
  name: "Text to columns (regular expression separator)",
  functionName:"textToColumnsRegExp"
});


,并添加了引用的函数:

function textToColumnsRegExp() {
  var separator = Browser.inputBox(
    "Text to column",
    "Enter the regular expression for the separator ",
    Browser.Buttons.OK);
  if (separator) {
    textToColumns(new RegExp(separator));
  }
}


没有其他由于Evan使用Javascript的String.prototype.split,该字符串接受字符串或RegExp对象作为分隔符,因此需要进行更改。埃文很荣幸!

#6 楼

此外,在使用提供包含分隔值的数组的SPLIT函数之后,可以使用INDEX函数从该数组中隔离特定的行或列:

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


例如,对于检索Google查询的结果数量可能很有用

#7 楼

似乎有一个菜单项可以帮助您解决此问题,位于:数据→将文本拆分为列...



单击单元格并粘贴数据。

它将显示在多行中,但仅显示一列。


突出显示新填充的单元格,进入菜单数据→将文本拆分为列。 。

如果应用程序成功检测到分隔符,那么恭喜您:完成!

否则,将出现一个小部件,询问您要分割的分隔符

注意:此窗口小部件可能会出现在窗口底部附近,因此很难找到它!逗号,分号,句点,空格或自定义。
如果要在选项卡上拆分:



#8 楼

我编写了此函数以拆分特定的列。
首先,获取参考表

function SplitColumnValues(sheet_name, column_index, delimiter) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheet_name);
  var lastRow = sheet.getLastRow();

  var range = sheet.getRange(1,column_index,lastRow-1, 1);
  range.splitTextToColumns(delimiter);
}


下一行将获取该列中需要拆分的行数

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheet_name);


下一步获取范围(即需要拆分的列),然后使用'splitTextToColumn'拆分该范围。

var lastRow = sheet.getLastRow();


#9 楼

而columnToText的等效项是使用=JOIN(delim, array)公式。例如,=JOIN(",", A1:A10)将导致它将来自单元格A1到A10的值的字符串连接在一起。

评论


是的,但是OP表示它在一个单元格中具有值(逗号分隔)。

–雅各布·扬·图恩斯特拉(Jacob Jan Tuinstra)
2013年9月8日在12:21

有用的信息,但最好将其发布为我猜想的答案之一下的评论。由于这不能回答原始问题。

–大卫
2015年7月22日,1:11