# Combine text from multiple cells, Based on conditions

1. ## Combine text from multiple cells, Based on conditions

Hello friends,

Following is a small block of data. I need to write a formula, such that

Output in B1

If A1="All Segments",
Combine A2, A3, A4, A5 (into B1).....till another "All Segments" is encountered
If A1<>"All Segments", return '0'

X A B
1 All Segments India, Outside India
2 India 0
3 Outside India 0
4 All Segments Formalin, Methanol, Siliguri
5 Formalin 0
6 Methanol 0
7 Siliguri 0
8 All Segments Domestic, Exports
9 Domestic 0
10 Exports 0
11 All Segments
12 Cattle & Poultry Feed
13 Poultry
14 Sugar & Related Activities
16 All Segments
17 Insulation
Any help would be appreciated. Thanks in anticipation.

2. ## Re: Combine text from multiple cells, Based on conditions

Hi
Array formula below concaniates up to 4 words
=IF(B2="All Segments",TRIM(IFERROR(INDEX(OFFSET(B2,1,0,MATCH("All Segments",\$B3:\$B19,0)-1,1),1),"")&" "&IFERROR(INDEX(OFFSET(B2,1,0,MATCH("All Segments",\$B3:\$B19,0)-1,1),2),"")&" "&IFERROR(INDEX(OFFSET(B2,1,0,MATCH("All Segments",\$B3:\$B19,0)-1,1),3),"")&" "&IFERROR(INDEX(OFFSET(B2,1,0,MATCH("All Segments",\$B3:\$B19,0)-1,1),4),"")),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1