Forum Discussion
TRANSPOSE without zeroes has 255 character limit
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.
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.
2 Replies
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.
Thank you for your reply. Your suggestion of using TRANSPOSE(A1:C1&"") worked well for me.
