TRANSPOSE without zeroes has 255 character limit

[Pezaul]

Hi,

I am using the TRANSPOSE function with an extra check for blank cells to avoid getting zeroes, i.e.:

TRANSPOSE(IF(A1:C1="",A1:C1,""))

However, if one of the cells being transposed has more than 255 characters, the resulting transposed cell displays #VALUE!

The problem does not appear if I use a straightforward TRANSPOSE function such as: 
TRANSPOSE(A1:C1). However, this causes blank cells to appear as 0, which I want to avoid.

This looks like it could be a bug (or remnants of an old limitation on cell character limits).

Thanks in advance to anyone who can help.

[#2 · Accepted solution]

@Pezaul 

The problem is not in TRANSPOSE but in IF. The formula =IF(A1:C1<>"",A1:C1,"") will also return #VALUE! in the third cell.

You could use =TRANSPOSE(A1:C1&"") but that will convert numbers to text.

[#3]

@Hans Vogelaar 

Thank you for your reply. Your suggestion of using TRANSPOSE(A1:C1&"") worked well for me.