英文:
How can I re-organize cells containing partial text from different cell into one Colum?
问题
Certainly, here's the translated content:
| header 1 | header 2 | header 3 | 等等... |
|---|---|---|---|
| 'borough': 'ab' | 'postal_code': 'm1' | 'street': 'bbb' | ... |
| 'borough': 'mz' | 'postal_code': '76' | 'street': 'aaa' | ... |
| 'borough': 'an' | 'postal_code': 'in' | 'street': 'sda' | ... |
| 'borough': 'bb' | 'postal_code': '76' | 'street': 'tre' | ... |
| 等等... | ... | ... | ... |
Hi.
考虑上面的表格。我该如何将单元格组织成下面的表格？
| header 1 | header 2 | header 3 | 等等... |
|---|---|---|---|
| 'borough': 'ab' | 'postal_code': 'm1' | 'street': 'bbb' | ... |
| 'borough': 'mz' | 'postal_code': '76' | 'street': 'aaa' | ... |
| 'borough': 'an' | 'postal_code': 'in' | 'street': 'sda' | ... |
| 'borough': 'bb' | 'postal_code': '76' | 'street':'tre' | ... |
| 等等... | ... | ... | ... |
我有一个包含许多单元格的大型Excel表格，我想将包含来自不同单元格的部分文本的单元格重新组织到一列中。
英文:
| header 1 | header 1 | header 3 | etc... |
|---|---|---|---|
| 'borough': 'ab' | 'postal_code': 'm1' | 'street': 'bbb' | ... |
| 'street': 'aaa' | 'borough': 'mz' | 'postal_code': '76' | ... |
| 'borough': 'an' | 'street': 'sda' | 'postal_code': 'in' | -- |
| 'street':'tre' | 'postal_code': '76' | 'borough': 'bb' | ... |
| etc.... | ... | ... | ... |
Hi.
consider the above table. how can I organize the cells like the bellow table?
| header 1 | header 2 | header 3 | etc... |
|---|---|---|---|
| 'borough': 'ab' | 'postal_code': 'm1' | 'street': 'bbb' | ... |
| 'borough': 'mz' | 'postal_code': '76' | 'street': 'aaa' | ... |
| 'borough': 'an' | postal_code': 'in' | 'street': 'sda' | ... |
| 'borough': 'bb' | 'postal_code': '76' | 'street':'tre' | ... |
| etc.... | ... | ... | ... |
I have a large Excel sheet containing many cells and I want to re-organize cells containing partial text from different cells into one Colum.
答案1
得分: 2
Formula in E2:
=LET(x,A2:C5,y,TOCOL(x),WRAPCOLS(SORTBY(y,TEXTBEFORE(y,";")),ROWS(x)))
英文:
Try:
Formula in E2:
=LET(x,A2:C5,y,TOCOL(x),WRAPCOLS(SORTBY(y,TEXTBEFORE(y,":")),ROWS(x)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论