join texts

[sna]

Hi Dear,

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

Thanks

[snasui]

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

[Supachok]

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)))

[sna]

Hi Dear,

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

[snasui]

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'.

[sna]

Thank you,it is awesome