Thread: filter by length of cell contents (2000)

1. filter by length of cell contents (2000)

Some accounts in column D have a length of 5, others have a length of 12. I would like to subtotal the accounts with a length of 5 separately, but I can't figure out how to filter by length. Can anyone help?

Thanks very much,
Sue

2. Re: filter by length of cell contents (2000)

Hi Sue..In a new column, say E, type =len(D1) and fill down the entire row of data. This will give the character lengths of the strings of text in each cell of column D. Sort Column E in Ascending order and you will now be able to subtotal for every change of value in column E.

3. Re: filter by length of cell contents (2000)

Jerry,

Did you want to say =sumif(E1:E100,5,D1100) instead of countif to get the subtotal?

4. Re: filter by length of cell contents (2000)

Yes I did, thanks <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>.....I will change the original post

5. Re: filter by length of cell contents (2000)

<P ID="edit" class=small>(Edited by Jezza on 23-Aug-05 22:35. Item in red now changed from countif...sorry Sue)</P>Sorry Sue

In addition if you do not want to sort in ascending or descending you can create subtotals by adding this in a seperate field

<span style="background-color: #FFFFFF;"><font color=red>=sumif(E1:E100,5,D1100) </font color=red></span hiwhite>

this will count the number of 5's in column E...adjust the range to fit your data.

6. Re: filter by length of cell contents (2000)

and just for clarity, I should point out that in the sumif() formula, the D1100 represents whatever value it is that is desired to be summed.

In Sue's example and in yours, column D contains the account numbers and is not likely what she is desiring to add together, so that was probably a bad example on my part. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

7. Re: filter by length of cell contents (2000)

Brett

I reckon it must be account numbers with the varying lengths and the values for sub-totalling are in another column (just guessing). Your/our suggestion will suffice though as Sue can adjust the column to fit our example. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

8. Re: filter by length of cell contents (2000)

To atone for my earlier wrong posting, now deleted, you can also use the following array formula without intermediate fomulas:

{=SUM(IF(LEN(E5:E105)=5,E5:E105,))}

do not copy the {} curly parens, instead enter the formula using Ctrl-Shift-Enter. Edit E5:E105 to the correct range.

9. Re: filter by length of cell contents (2000)

Why not try using an autofilter and then use a custom filter to find all records of the form ?????. This will work for text fields but not (I think) on numeric. If the data is numeric, you could filter for records where the value is greater than 9999 and less than 100000.

10. Re: filter by length of cell contents (2000)

Thanks to all who responded to my question. I was able to get subtotals by sorting on length of the account field, but I was not able to make the SUMIF suggestions work. My account numbers are a numeric field, not text, because when I make the field text, it goes into scientific notation.
My account numbers are in D242. The amounts that I would like to sum are in F2:F42. When I tried the formula=sumif(D242,5,F2:F42), i get a result of 0.
When I try =SUM(IF(LEN(D242)=5,F2:F42)), I get #VALUE. Can anyone tell me what I am doing wrong.

Thanks very much, Sue

11. Re: filter by length of cell contents (2000)

With =SUM(IF(LEN(D242)=5,F2:F42)) you need to make sure you press control-shift-enter after typing in the formula to make it an array formula.

With the SUMIF, you have to follow Jezza's initial instructions of typing the formula in a blank column to show the length of the characters in each cell, then change the D242 in the formula to whatever column your LEN formula is in.

HTH

12. Re: filter by length of cell contents (2000)

Sue,
Did you enter the formula using Ctrl-Shift-Enter rather than just Enter?

13. Re: filter by length of cell contents (2000)

It helps enormously. Both ways worked perfectly.
Thanks again to all who responded. This site is definitely the best for prompt and helpful assistance.

Sue

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•