如何在excel中连接单元格(行)，直到找到一个值? [英] How to concatenate cell (row wise) in excel till a value is found?

问题描述

我在MS excel工作表中的数据为:

I have data in MS excel sheet as :

Row     Column A
1   AAA Number of ABC
2   Streaming Call
3   Explanation
4   BBB Number of ABC
5   Streaming Call
6   from source
7   Explanation
8   CCC Number of ABC
9   Streaming Call
10  from source
11  to Dest
12  Explanation
...
...
196000  Explanation

我想在B列中继续连接A列的单元格，直到该单元格值为"Explanation"为止:

I want to keep concatenating cell of column A in column B, till the cell value is "Explanation" :

1   AAA Number of ABC   AAA Number of ABC
2   Streaming Call          AAA Number of ABC Streaming Call
3   Explanation         Explanation
4   BBB Number of ABC   BBB Number of ABC
5   Streaming Call          BBB Number of ABC Streaming Call
6   from source         BBB Number of ABC Streaming Call from source
7   Explanation         Explanation
8   CCC Number of ABC   CCC Number of ABC
9   Streaming Call          CCC Number of ABC Streaming Call
10  from source         CCC Number of ABC Streaming Call from source
11  to Dest                 CCC Number of ABC Streaming Call from source to Dest
12  Explanation         Explanation
...
....

有人可以建议我粘贴在B列中并拖动以得到所需结果的公式吗?

Can somebody suggest me formula that I can paste in Column B and drag to get desired result?

谢谢.

推荐答案

不愿意为您编写代码，但这很简单，所以为什么不呢?放到B2中:

Reluctant to just write code for you, but it's an easy one so why not... Put in B2:

=IF(ROW()=1,A2,IF(A2="Explanation","Explanation",CONCATENATE(IF(A1="Explanation","",B1),A2)))

上下拖动.

这篇关于如何在excel中连接单元格(行)，直到找到一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！