Forum Discussion
Adrian_Booth
May 25, 2022Copper Contributor
Using an array formulas to return more than 255 characters of text based on a condition in excel
I use excel spreadsheets to monitor performance on a quarterly basis. In addition to numeric values, reports include a descriptions/narratives. However, sometimes the narrative information provided...
mtarler
May 25, 2022Silver Contributor
That is a limitation of Array Formulas.
My suggestion is to make your Table formatted as a Table (Home -> Format as a Table) and then when you enter a formula into a column it auto-populates the whole column so each formula is a 'single' value that looks at @[column] per se and appears (in my test) to have no problem returning x-long strings.
My suggestion is to make your Table formatted as a Table (Home -> Format as a Table) and then when you enter a formula into a column it auto-populates the whole column so each formula is a 'single' value that looks at @[column] per se and appears (in my test) to have no problem returning x-long strings.
- Adrian_BoothMay 25, 2022Copper ContributorThank you for your suggestion.
For the specific application I have found using format as tables limiting and have shied away from it. If there is no other way I may have to resort to it.
I have found using the LEFT formula as a surprising workaround to successfully return x-long strings in array formulas (e.g. LEFT(A1:A500,5000). However, when seeking to add conditions with IF or IFS, it doesn't work (#Value error).