Concatenate, If, blanks, brackets and text

[gingerexcel]

Hi

I have spent far too long on this.....

I have up to 5 columns that I want to concatenate. Some are blank, some have values.

I always want to concatenate A2 and B2 (straightforwards), then I want a s. in front of C2, then only if not blank, D2 should have brackets around it, and only if not blank, E2 should have brackets around it.

I get close, but I can't work out a way to handle the combination of blanks and not blanks without ending up with a rogue close brackets or open brackets. The spreadsheet has a few thousand rows.

Thanks so much, genii

 

[Joe4]

Welcome to the Board!

Why doesn't the third example have "1971" in the desired result?
Is that a typo, or is there more logic there that needs to be accounted for?

 

[Joe4]

If that was just a typo and the 1971 should be there, try this formula for an entry on row 2:

=A2&" "&B2&IF(C2<>""," s."&C2,"")&IF(D2<>""," ("&D2&")","")&IF(E2<>"","("&E2&")","")

 

[gingerexcel]

Welcome to the Board!

Why doesn't the third example have "1971" in the desired result?
Is that a typo, or is there more logic there that needs to be accounted for?

Sorry it was a typo! Corrected version below.

 

[Joe4]

OK, my formula above should do what you want.

 

[Fluff]

Another option

=TEXTJOIN(" ",,A2:B2,IF(C2="","","s."&C2),IF(D2:E2="","","("&D2:E2&")"))

 

[gingerexcel]

If that was just a typo and the 1971 should be there, try this formula for an entry on row 2:

=A2&" "&B2&IF(C2<>""," s."&C2,"")&IF(D2<>""," ("&D2&")","")&IF(E2<>"","("&E2&")","")

Genius, thank you, you've saved me!

 

[Joe4]

You are welccome!
Glad we were able to help!