格式化原始文本（来自Google表单响应）成排和列的Google表格和附加页面

发布于 2025-02-10 09:54:58 字数 926 浏览 3 评论 0原文

我有一个Google表格，其中我正在以以下格式捕获数据。

Name:Karslten [0023]
Level:1
You managed to complete:
Burpies:30
Squats:45
Bench:29
Crunch:43
TotalLoss: 981

Name:Jaeger [0119]
Level:3
You managed to complete:
Burpies:40
Squats:53
Bench:45
Crunch:53
TotalLoss: 1253
Name:Fostyne [0003]
Level:1
You managed to complete:
Burpies:24
Squats:38
Bench:25
Crunch:32
TotalLoss: 758

我想知道是否可以使用Google表公式使用数据并以特定方式将其格式化为行和列。一种表单条目可以包含多个人的数据。

我想要达到的格式应该看起来像这样，所有新条目都必须附加到新的结果表中。

我想要的Google Sheect的格式的屏幕快照

这是一个链接到DAMMAIDED EDAMDEASTAILE EDAIMETAIL GOGEE SHEETS SHEETS 与其他数据相同的数据。我希望有些知识渊博的人能帮助我实现这一目标。预先感谢您。

原文

I have a Google Forms where I'm capturing data in the following format.

Name:Karslten [0023]
Level:1
You managed to complete:
Burpies:30
Squats:45
Bench:29
Crunch:43
TotalLoss: 981

Name:Jaeger [0119]
Level:3
You managed to complete:
Burpies:40
Squats:53
Bench:45
Crunch:53
TotalLoss: 1253
Name:Fostyne [0003]
Level:1
You managed to complete:
Burpies:24
Squats:38
Bench:25
Crunch:32
TotalLoss: 758

I would like to know if it will be possible to use Google Sheets formula to use the data and format it into rows and columns in a specific way. One form entry can contain data for multiple persons.

The format I want to achieve should look like this and all new entries must be appended to the new result sheet.

Screenshot for the format I want from Google Sheets

Here is a link to a dummy editable Google Sheets with the same data. I hope some knowledgeable people can help me achieve this. Thanking you in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风吹过旳痕迹 2025-02-17 09:54:58

尝试：

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(TRIM(REGEXREPLACE(
 'Form Responses 1'!B2:B&CHAR(10)&SPLIT(SUBSTITUTE('Form Responses 1'!C2:C, 
 "Name:", "×Name:"), "×"), "(.+:)", ))), CHAR(10))), "where Col2 is not null", ))

try:

=ARRAYFORMULA(QUERY(IFERROR(SPLIT(FLATTEN(TRIM(REGEXREPLACE(
 'Form Responses 1'!B2:B&CHAR(10)&SPLIT(SUBSTITUTE('Form Responses 1'!C2:C, 
 "Name:", "×Name:"), "×"), "(.+:)", ))), CHAR(10))), "where Col2 is not null", ))