SOLVED

TRANSPOSE without zeroes has 255 character limit

%3CLINGO-SUB%20id%3D%22lingo-sub-2836354%22%20slang%3D%22en-US%22%3Etranspose%20without%20zeroes%20has%20255%20character%20limit%20when%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836354%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20TRANSPOSE%20function%20with%20an%20extra%20check%20for%20blank%20cells%20to%20avoid%20getting%20zeroes%2C%20i.e.%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETRANSPOSE(IF(A1%3AC1%3D%22%22%2CA1%3AC1%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20if%20one%20of%20the%20cells%20being%20transposed%20has%20more%20than%20255%20characters%2C%20the%20resulting%20transposed%20cell%20displays%20%23VALUE!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20does%20not%20appear%20if%20I%20use%20a%20straightforward%20TRANSPOSE%20function%20such%20as%3A%26nbsp%3B%3CBR%20%2F%3ETRANSPOSE(A1%3AC1).%20However%2C%20this%20causes%20blank%20cells%20to%20appear%20as%200%2C%20which%20I%20want%20to%20avoid.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20looks%20like%20it%20could%20be%20a%20bug%20(or%20remnants%20of%20an%20old%20limitation%20on%20cell%20character%20limits).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20to%20anyone%20who%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2836354%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2837124%22%20slang%3D%22en-US%22%3ERe%3A%20TRANSPOSE%20without%20zeroes%20has%20255%20character%20limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182683%22%20target%3D%22_blank%22%3E%40Pezaul%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20problem%20is%20not%20in%20TRANSPOSE%20but%20in%20IF.%20The%20formula%20%3DIF(A1%3AC1%26lt%3B%26gt%3B%22%22%2CA1%3AC1%2C%22%22)%20will%20also%20return%20%23VALUE!%20in%20the%20third%20cell.%3C%2FP%3E%0A%3CP%3EYou%20could%20use%20%3DTRANSPOSE(A1%3AC1%26amp%3B%22%22)%20but%20that%20will%20convert%20numbers%20to%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2840159%22%20slang%3D%22en-US%22%3ERe%3A%20TRANSPOSE%20without%20zeroes%20has%20255%20character%20limit%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2840159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20Your%20suggestion%20of%20using%20%3CSPAN%3ETRANSPOSE(A1%3AC1%26amp%3B%22%22)%26nbsp%3B%3C%2FSPAN%3Eworked%20well%20for%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@Hans Vogelaar 

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