Oct 01 2020 02:23 AM - edited Oct 01 2020 02:27 AM
I used the FILTER formula to filter few row that contain txt.
However, when I use IFERROR to avoid getting error, cells that have more than 256. How can I avoid this error?
Oct 01 2020 02:38 AM
Could you please attached the sample data & share the screenshot of the error you are getting
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer and like to help the other members find it more
Oct 01 2020 02:52 AM
Cannot attach an excel sheet.
Filter returns
=Filter(A:B,A:A="Office")
A | B |
Office | less than 255 character |
Office | less than 255 character |
Office | less than 480 character |
Office | less than 255 character |
Now if I club this formula with IFERROR, it only returns
Formula - Iferror(Filter(A:B,A:A="Office"),"NA")
A | B |
Office | less than 255 character |
Office | less than 255 character |
Office | NA |
Office | less than 255 character |
Oct 01 2020 03:16 AM
The formula is working fine, I have attached the sample file for your reference.
=IFERROR(FILTER($B$10:$C$13,$B$10:$B$13="Office"),"NA")
If you are unable to resolve the issue request you to please re-create the file removing the confidential information and upload the same
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer and like to help the other members find it more
Oct 01 2020 04:11 AM
I have a look at the screenshot. My problem is that when a cell has more than 255 characters then excel returns error.
I have used LEN function to calculate total characters
Oct 01 2020 04:40 AM
You are right, @VishalRathore .
Actually, Filter - Iferror Functions Combination doesn't return cell text value if the number of characteristics is more than 256.
Should find solution:)
Oct 01 2020 05:00 AM
Please follow the below link
http://excel.tips.net/T003163_Charac...for_Cells.html
Just mention that you can enter up to 32,767 characters in a cell of later versions of Excel and display 1 024 characters in a cell.
Oct 01 2020 11:14 AM
Interesting question. That's not only with IFERROR, with any function which works with arrays. For example
My guess array element can't be more than 256 in size. In you sample, FILTER itself returns the range into the sheet. If we wrap it with another function it returns an array to that function and #VALUE! appears for too big element of the array.
Same in my sample, C#:C6 first return an array to IF() and within it we have error for large (in size) element. Simple =C3:C6 works.
Not sure about workaround if it exists.
Oct 03 2020 09:24 AM
In addition. I guess from practical point of view IFERROR() is needed to replace #CALC error if filter is not match by some text. If so, the workaround could be
=IF(COUNTIFS(A:A,"Office"), FILTER(B:B,A:A="Office"), "NA")