Help finishing array formula!

[Guest]

I need the following array formul to only select cell values longer than a
string of 4. The array works-but I cannot get it to discriminate and only
select values of 5 characters and larger
=IF(ISERROR(LARGE(D614,ROW(INDIRECT("1:4")))),"",LARGE(D614,ROW(INDIRECT("1:4"))))
Ex. Col A
4444>Don't show
45656>Show value
123>Don't Show
45689>Show value
Thanks for the help.

 

[Guest]

To test for the length of a text string, use the function LEN: =LEN(A1)
returns the number of characters in A1.

So, if you're testing for text strings longer than 5 characters you would
want something like =IF(LEN(A1)>5,"VALID","INVALID")

I'm not sure how that would fit into your formula below as I'm not sure what
you're trying to accomplish with the INDIRECT and LARGE functions.

Dave

 

[Guest]

Thanks for the response Dave. I tried the len() function-but keep getting an
error message that I have added too many arguments. I solved it by using the
len() with conditional formatting and [hiding] the values I do not want
printed. So I cheated!
Thanks again for the response.

 

[Bob Phillips]

=IF(ISERROR(SMALL(IF(LEN($D$6$14)>4,ROW($D$6:$D$14),""),ROW($D$6:$D$14)-MI
N(ROW($D$6:$D$14))+1)),"",
INDEX($D$1:$D$14,SMALL(IF(LEN($D$6$14)>4,ROW($D$6:$D$14),""),ROW($D$6:$D$1
4)-MIN(ROW($D$6:$D$14))+1)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks for the response Dave. I tried the len() function-but keep getting an
error message that I have added too many arguments. I solved it by using the
len() with conditional formatting and [hiding] the values I do not want
printed. So I cheated!
Thanks again for the response.

To test for the length of a text string, use the function LEN: =LEN(A1)
returns the number of characters in A1.

So, if you're testing for text strings longer than 5 characters you would
want something like =IF(LEN(A1)>5,"VALID","INVALID")

I'm not sure how that would fit into your formula below as I'm not sure what
you're trying to accomplish with the INDIRECT and LARGE functions.

Dave

=IF(ISERROR(LARGE(D614,ROW(INDIRECT("1:4")))),"",LARGE(D614,ROW(INDIRECT
("1:4"))))

 

[Guest]

I didn't think of this-works great-much thanks.

=IF(ISERROR(SMALL(IF(LEN($D$6$14)>4,ROW($D$6:$D$14),""),ROW($D$6:$D$14)-MI
N(ROW($D$6:$D$14))+1)),"",
INDEX($D$1:$D$14,SMALL(IF(LEN($D$6$14)>4,ROW($D$6:$D$14),""),ROW($D$6:$D$1
4)-MIN(ROW($D$6:$D$14))+1)))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Thanks for the response Dave. I tried the len() function-but keep getting an
error message that I have added too many arguments. I solved it by using the
len() with conditional formatting and [hiding] the values I do not want
printed. So I cheated!
Thanks again for the response.

To test for the length of a text string, use the function LEN: =LEN(A1)
returns the number of characters in A1.

So, if you're testing for text strings longer than 5 characters you would
want something like =IF(LEN(A1)>5,"VALID","INVALID")

I'm not sure how that would fit into your formula below as I'm not sure what
you're trying to accomplish with the INDIRECT and LARGE functions.

Dave
--
Brevity is the soul of wit.


:

I need the following array formul to only select cell values longer than a
string of 4. The array works-but I cannot get it to discriminate and only
select values of 5 characters and larger.

=IF(ISERROR(LARGE(D614,ROW(INDIRECT("1:4")))),"",LARGE(D614,ROW(INDIRECT
("1:4"))))

Ex. Col A
4444>Don't show
45656>Show value
123>Don't Show
45689>Show value
Thanks for the help.