Filter for length of text

FredMFoley · Mar 19th, 2002, 09:57 PM

Can you use filtering to select text of a given length - eg 3 characters long? (or is VBA needed)
Thank You

Tom Urtis · Mar 19th, 2002, 10:25 PM

You can do this without VBA, using an AutoFilter. For the criteria, select "Custom". In the Custom AutoFilter dialog box's first field, choose "equals", and in the criteria field to the right of that, enter three question marks (???), to represent 3 characters as in your specific example. That will filter for cells in that column containing 3 characters.

yussi1870 · Mar 21st, 2002, 09:11 AM

For even more flexibility you could create a second column with the following formula in it:

=len([firstcolumn,firstrow])

then pull that formula down to all the rows containing the data.

This will give you the length of each entry in the column containing your data. You can then custom filter on the length. You can set the filter to show you rows which have less than, less than or equal to, greater than, greater than or equal to, or between a certain number of characters. So this filter is set on the column containing the lenght formulas but effects the column containing your data.

Mar 19th, 2002, 10:25 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	You can do this without VBA, using an AutoFilter. For the criteria, select "Custom". In the Custom AutoFilter dialog box's first field, choose "equals", and in the criteria field to the right of that, enter three question marks (???), to represent 3 characters as in your specific example. That will filter for cells in that column containing 3 characters. __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 09:57 PM	#1
FredMFoley Board Regular Join Date: Mar 2002 Location: Tasmania Posts: 58	Can you use filtering to select text of a given length - eg 3 characters long? (or is VBA needed) Thank You

Mar 21st, 2002, 09:11 AM	#3
yussi1870 Board Regular Join Date: Mar 2002 Posts: 128	For even more flexibility you could create a second column with the following formula in it: =len([firstcolumn,firstrow]) then pull that formula down to all the rows containing the data. This will give you the length of each entry in the column containing your data. You can then custom filter on the length. You can set the filter to show you rows which have less than, less than or equal to, greater than, greater than or equal to, or between a certain number of characters. So this filter is set on the column containing the lenght formulas but effects the column containing your data.