Google表格:列和行的字符串 [英] Google Sheets: string to columns and rows

问题描述

我有一个用逗号分隔的字符串.通过拆分公式，我可以轻松地将每个数据放在单独的单元格中，但所有数据都在一行中.我需要从这种格式转换数据:

I have a string separated by commas. Easily with split formula I can have each data in separated cells but all the data are in one single row. I need to transform data from this format:

为此格式:

其中，列F中的值仅与一行相关联.因此，这意味着原始字符串中只有一个块比其他块长.遵循以上屏幕截图:

where the value in the column F is associated to only one row. So it means that one and only one block of the original string is longer than the others. Following the above screenshots:

字符串的第一个块: fubles.com/matches/2655533，萨拉图竞技场，爱德华多·帕拉维奇尼，7.38,6，

1st block of the string: fubles.com/matches/2655533,Atletico Saraitu,Edoardo Pallavicini,7.38,6,

字符串的第7个块: fubles.com/matches/2655533，马德里竞技竞技场，安德里亚·西塞洛，7.55,7，是的，

7th block of the string: fubles.com/matches/2655533,Atletico Saraitu,Andrea Cissello,7.55,7,Yes,

有没有人可以帮助我建立正确的公式?

Is there anyone that can help me to build the right formula?

谢谢

推荐答案

您可以分两部分进行操作-首先将不同的行分开-然后在保存值后在行上再次运行split-使用replace或regexreplace可以添加一个不同的定界符，我更喜欢使用分号:

You can do it in 2 parts - first to separate the different rows - then run split again on the rows after you save the values - use substitute or regexreplace to add in a different delimiter, i prefer to use the semicolon:

=transpose(split(REGEXREPLACE(A1,"fubles",";fubles"),";"))

OR

=transpose(split(SUBSTITUTE(A4,"fubles",";fubles"),";"))

从字面上看，它们确实做同样的事情，但实际上，您是在起始字符串的末尾添加分号，然后按该定界符进行拆分，然后进行转置以堆积行.

They literally do the exact same thing, but essentially you are adding the semicolon in from of your starting string, then you split by that delimiter, the transpose to stack the rows.

然后您应该进行COPY和PASTE VALUES来展平数据-然后在所有这些行上进行逗号分隔.

You should then COPY and PASTE VALUES to flatten the data - then run your split by comma on all those rows.

这篇关于Google表格:列和行的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！