Help finishing array formula!
 

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(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4"))))
Ex. Col A
4444Don't show
45656Show value
123Don't Show
45689Show value
Thanks for the help.

Help finishing array formula!
 

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.


"Pat Flynn" wrote:

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(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4"))))
Ex. Col A
4444Don't show
45656Show value
123Don't Show
45689Show value
Thanks for the help.

Help finishing array formula!
 

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.

"Dave F" wrote:

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.


"Pat Flynn" wrote:

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(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT("1:4"))))
Ex. Col A
4444Don't show
45656Show value
123Don't Show
45689Show value
Thanks for the help.

Help finishing array formula!
 

=IF(ISERROR(SMALL(IF(LEN($D$6:D$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:D$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)

"Pat Flynn" wrote in message
...
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.

"Dave F" wrote:

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.


"Pat Flynn" wrote:

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(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT
("1:4"))))
Ex. Col A
4444Don't show
45656Show value
123Don't Show
45689Show value
Thanks for the help.

Help finishing array formula!
 

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

"Bob Phillips" wrote:

=IF(ISERROR(SMALL(IF(LEN($D$6:D$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:D$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)

"Pat Flynn" wrote in message
...
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.

"Dave F" wrote:

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.


"Pat Flynn" wrote:

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(D6:D14,ROW(INDIRECT("1:4")))),"" ,LARGE(D6:D14,ROW(INDIRECT
("1:4"))))
Ex. Col A
4444Don't show
45656Show value
123Don't Show
45689Show value
Thanks for the help.