转置，拆分和合并Google表格 [英] Transpose, Split, and Join Google Sheets

问题描述

我正在使用Google表单，以便用户输入6位数字的列表.我需要将这些数字转移到另一个Google表格中，但在此之前，我希望它们位于响应Google表格中的列"中.我知道我必须移调，基于，"分割和加入所有响应，因为这将每天进行.到目前为止，我已经尝试过:

I am using Google forms so that the user inputs a list of 6 digit numbers. I need to transfer those numbers to another Google Sheet but before I do that, I would like them to be in a Column on the responses Google Sheet. I know I have to Transpose, Split based on ",", and Join all responses since this will be done daily. So far I've tried:

= TRANSPOSE(SPLIT(JOIN(，" B2:B)B2:B，，"，TRUE，TRUE)B2:B)

=TRANSPOSE(SPLIT(JOIN("," B2:B)B2:B, ",", TRUE, TRUE)B2:B)

但是我遇到了解析错误.

but I'm getting a parse error.

这是我正在使用的测试页的链接: https://docs.google.com/spreadsheets/d/1n5ZyTThvulFxve4 edit?usp = sharing

Here is a link to the test page I'm using: https://docs.google.com/spreadsheets/d/1n5ZyTThvulFxjb274qpzytgiplzve16DG4z8WPlqGoU/edit?usp=sharing

感谢您的帮助.

推荐答案

此处使用此升级公式

= TRANSPOSE(SPLIT(REGEXREPLACE(JOIN(，"，((ArrayFormula("& TRIM(FILTER(B2:B，NOT(ISBLANK(B2:B))))))))，"，"')，"，))

=TRANSPOSE(SPLIT(REGEXREPLACE(JOIN(",",(ArrayFormula(" "&TRIM(FILTER(B2:B,NOT(ISBLANK(B2:B)))))))," ","'"),","))

任何数据处理工具都不允许您将"0"保留为数字的起始字符，即 如果将= 01110放在任何单元格中，则得出1110.

any data handling tool wont let you keep a '0' as the beginning charater of a number i.e. if you put =01110 in any cell,it would yield 1110.

但是，如果将其转换为字符串，则可以获得期望的输出，即 如果将='01110放在任何单元格中，则将得出01110. 注意'"单引号字符(数字到字符串).

But if you convert the same into a string the desire output can be obtained i.e. if you put ='01110 in any cell,it would yield 01110. Notice the " ' " single quote character(numerical to string).

我刚刚升级了公式，以生成这些QR码的字符串输出. 希望你一切都好.

I have just upgraded the formula to produce a string output of these QR code. Hope its fine by you.

这篇关于转置，拆分和合并Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！