Help finishing array formula!

  • Thread starter Thread starter Guest
  • Start date Start date
G

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(D6:D14,ROW(INDIRECT("1:4")))),"",LARGE(D6:D14,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.
 
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
 
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.
 
=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 said:
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 said:
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
Click to expand...
=IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"",LARGE(D6:D14,ROW(INDIRECT
("1:4"))))
Click to expand...
 
I didn't think of this-works great-much thanks.

Bob Phillips said:
=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 said:
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 said:
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.
Click to expand...
=IF(ISERROR(LARGE(D6:D14,ROW(INDIRECT("1:4")))),"",LARGE(D6:D14,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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top