Hide a hyphen used in Concatenate.

[Simon Blackburn]

I am using a hyphen (-) in the concatenate function to link cells A1 & A2 into Cell A3,

Eg, =CONCATENATE(A1," - ",A2).

When the cells A1 &A2 have no data in them, how can I prevent cell A3 from displaying just the hyphen without using VBA as sometimes, I need this cell to be completely blank for display purposes.

Thanks in advance.

[#2]

Hello,

maybe like this:

=IF(COUNTA(A1:A2)=2,A1&" - "&A2,A1&A2)

The CountA() function counts how many cells in the range have text. If that result is 2, then it performs the concatenation. If the result is not 2, it concatenates the cells without delimiter. This will return a blank cell if both cells are blank, and if only one cell has a value, it will return that value.

I prefer the & operator to concatenate text. It's far less typing. 

Let me know if that helped.

[#3]

Simon,

you could use TEXTJOIN():

=TEXTJOIN("-",TRUE,A1:A2)

[#4]

That's right. TextJoin is a great alternative, but it is only available in Excel 2016, not earlier versions.