Snasui Excel Forum

Hi Dear,

I need your help how to CONCAT text down as in attached file .the result is in colour cells

Thanks

Please provide all conditions for each result in cell B11 and B13.

sna wrote: Thu Dec 09, 2021 11:22 am Hi Dear,

I need your help how to CONCAT text down as in attached file .the result is in colour cells

Thanks

Try this formula in B2

=IF(B2="","",IF(COUNTA(B2:B4)>1,(CONCATENATE(A2," ",A3)),CONCATENATE(A2," ",A3," ",A4)))

Hi Dear,

The condition is Concat name with Mr or Mrs down until Mr or Mrs in next line

For Excel 2019+ in B2 try this formula,

=IF(COUNT(MATCH((LEFT(A2,{3,4})),{"Mr ","Mrs "},0)),CONCAT(INDEX(OFFSET(A2,0,0,IFERROR(MATCH("Mr*",$A3:$A$15,0),16-ROW()))&" ",0)),"")

16 is row number of first blank row after last record.

or

=IF(LEFT(A2,2)<>"Mr","",CONCAT(INDEX(A2:INDEX(A2:A$16,MATCH("Mr*",A3:$A$16&"Mr",0))&" ",0)))

When middle name not precede with 'Mr'.

Thank you,it is awesome