How do you perform a conditional concatenate in Excel? 1

[PaultheITGuy]

Hello,

I have created a fairly basic spreadsheet to keep tabs on all the sheet music I use with my band at church.

Often songs and hymns are sorted in books by their first line rather than their actual published title (e.g. the song "God Of Wonders" is sorted by its first line "Lord Of All Creation") and therefore I want to record both. Column A is called "First Line" and column B is "Title (If Different)".

I have a concatenate function in column C which combine these by listing the First Line, then a space, and then the Title in parenthesis so the finished result would look like this "Lord Of All Creation (God Of Wonders).

However, some songs (e.g. Deliverer) use their First Line as the Title and this means column A is populated but column B is blank and column C looks like this "Deliverer ()"

My current formula is

=CONCATENATE(A4, " (",B4,")")

and I have just used

=A4

for 'first line only' songs but this looks untidy as it brings up those annoying little green triangles which question your use of different formula in consecutive cells.

Can anyone tell me if there is there a way to adjust the formula so that if the Title in Column B is blank then it does not put the " ()" in but if it is populated then it includes them?

I'd be grateful for advice on this.

Many thanks

Paul C

 

[Diancecht]

Should be something like this

=IF(ISBLANK(B4);A4;CONCATENATE(A4, " (",B4,")"))

Cheers,
Dian

 

[GlennUK]

Or,

=A4&IF(B4<>""," ("&B4&")","")


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[PaultheITGuy]

Thanks Glenn - that worked a treat!

 

[GlennUK]

My pleasure. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.