# Filter by number preceding text

1. ## Filter by number preceding text

This has got to be simpler than I'm making it out to be, but I'm stuck. I have a column that is created by a formula that counts the number of times a name appears in a list and prepends the number to the beginning of the name. The result is something like:

A1:
1JohnSmith021488
2JohnSmith021488
1JaneDoe102187
2JaneDoe102187
3JaneDoe102187
4JaneDoe102187
5JaneDoe102187
6JaneDoe102187
7JaneDoe102187
8JaneDoe102187
9JaneDoe102187
10JaneDoe102187
11JaneDoe102187

I need a filter that results in a list of names preceded by "1" (but not 10, 11, 12, etc.) The correctly filtered result of the above list would be:

DESIRED:
1JohnSmith021488
1JaneDoe102187

My autofilter searches for data that begin with "1", so instead of the above result, I get:

UNDESIRED:
1JohnSmith021488
1JaneDoe102187
10JaneDoe102187
11JaneDoe102187

There's got to be an easy way to do this, but I can't figure how to account for the variable number of digits (1 or 2 digits) at the beginning. It would probably work to set up an advanced filter for cells that begin with a number less than 2, but I don't know how to do that. Any help would be greatly appreciated.

2. ## Re: Filter by number preceding text

I would put the count by itself in a seperate column, then concatenate the name and the count..

In A1, put
=COUNTIF(range,"JohnSmith")
In B1, put
=A1&"JohnSmith"

Then filter on column A for 1

3. ## Re: Filter by number preceding text

Try Advanced Filter using a formula as criteria

Something like

 A B C 1 Names Formula 2 1JohnSmith021488 TRUE 3 2JohnSmith021488 4 1JaneDoe102187 5 2JaneDoe102187 6 3JaneDoe102187 7 4JaneDoe102187 8 5JaneDoe102187 9 6JaneDoe102187 10 7JaneDoe102187 11 8JaneDoe102187 12 9JaneDoe102187 13 10JaneDoe102187 14 11JaneDoe102187

Formula in C2
=AND(LEFT(A2)="1",ISERR(0+LEFT(A2,2)))

Select A1:A14

Data > Advanced Filter
List Range: A1:A14
Criteria Range: C1:C2

Hope this helps

4. ## Re: Filter by number preceding text

I figured this out and I was right...I was making it far too complicated. I just created an advanced filter for =1* and <>10*, <>11*, <>12*, etc.

EDIT: I posted my solution before I saw the others, which are more elegant than mine. Thank you!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1