Combine text from multiple cells, but not when cell is blank

[jbparsons]

Hi There,

This is my first post, so hopefully I form the question correctly. I am using Excel 2008 for mac and would like to do the following.

Where I have text in A1, A2, A3 and sometimes in A4 and A5, for example:

A1 = Hello
A2 = Goodbye
A3 = Hello Again
A4 = (blank)
A5 = (blank)

I need a formula that combines the text from A1 to A5 into one cell, with "and" in between each set of text, but if any cell is blank does not include the "and" and then a space.

For example, when I use the formula:

=CONCATENATE(A1," and ",A2," and ",A3," and ",A4," and ",A5)

I of course get:

Hello and Goodbye and Hello Again and and

I would like to get:

Hello and Goodbye and Hello Again

But when A4 and A5 have text, for example:

A1 = Hello
A2 = Goodbye
A3 = Hello Again
A4 = Goodbye Again
A5 = Hello Again

That the same formula results in:

Hello and Goodbye and Hello Again and Goodbye Again and Hello Again

Is there a way to do this?

Thank you for your help!

[MarvinP]

Hi jbparsons and welcome to the forum,
Try this:

=IF(A1<>"", A1,"")& IF(A2<>""," and " & A2,"") & IF(A3<>""," and "& A3,"") & IF(A4<>""," and "&A4,"") & IF(A5<>""," and "&A5,"")

See the attached.

[jbparsons]

This is very helpful. I did come across a new issue though, which I didn't explain as a requirement in my first post. For the cells that come back blank, they are actually calling on data with a formula where if there is no data it will return "#N/A" rather than a blank cell. Is there any way to adjust this equation to still work the same way, if the cell has "#N/A" in it rather than a true blank cell?

Thanks again for the help!

[MarvinP]

Hi,
Look to see if you have an IfError function on that MAC 2008 version. That would be the direction I'd be going.