TRANSPOSE without zeroes has 255 character limit

New Contributor



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




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 Replies
best response confirmed by Pezaul (New Contributor)


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.

@Hans Vogelaar 

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