Is there a "LENIF" equivalent?

Question

I have a column populated with either "Very Small", "Small", or "Large". However, sometimes the system that creates this file adds extra characters in surprising locations. I'm wondering if there is a way to enter MAX and LEN with a conditional statement as an array formula (CTRL+SHIFT+ENTER (CSE)) so that it will give me the maximum character count only for cells beginning with "Very", for instance.

As an example, for a column in which I can expect a consistent LEN, I can enter

{=MAX(LEN(A1:A1000))}

using CSE to know if there are any outliers greater than that given LEN that I need to address. The question is whether or not there's a way to do this, but conditionally based on the cell contents within that range.

I'm using Excel on Office365.

"However, sometimes the system that creates this file adds extra characters in surprising locations". This sounds a little bit like an XY Problem, do you know why the system is adding extra characters? — BruceWayne
I don't. And I don't have access to that system to make or suggest changes, unfortunately. — rmbradburn

Scott Craner Scott Craner · Accepted Answer · 2020-01-08T15:16:43

Yes:

=MAX(LEN(IF(ISNUMBER(SEARCH("Very",A1:A1000)),A1:A1000," ")))

As an array formula confirmed with Ctrl-Shift-Enter, instead of Enter

Now if you have the new Dynamic Array formula:

=MAX(LEN(FILTER(A1:A1000,ISNUMBER(SEARCH("Very",A1:A1000))))

But if you simply want to "Clean" your data in a new column use:

=TRIM(CLEAN(A1))

And copy down.

Again with the new Dynamic Array formula one simple puts:

=TRIM(CLEAN(A1:A1000))

in the first cell of a new column and Excel will fill down.

Is there a "LENIF" equivalent?

1 Answers