+ Reply to Thread
Results 1 to 4 of 4

Filter by number preceding text

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Salem, OR
    MS-Off Ver
    Excel 2003
    Posts
    2

    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.
    Last edited by kimberwarden; 01-15-2014 at 02:17 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    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. #3
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    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
    Marcelo Branco

  4. #4
    Registered User
    Join Date
    09-11-2012
    Location
    Salem, OR
    MS-Off Ver
    Excel 2003
    Posts
    2

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to insert text in new column if preceding more than one of the preceding have val
    By aimeecrystalaid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2013, 11:47 PM
  2. Use LEFT formula to extract text preceding ONE OF TWO possible characters
    By potejam in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-22-2013, 08:05 PM
  3. Deleting spaces preceding text in copied in data
    By cloudwalking in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2013, 09:08 AM
  4. Replies: 7
    Last Post: 07-02-2012, 07:23 PM
  5. Preceding a number by zeros, that is still a number
    By Michele in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-14-2005, 09:05 AM

Tags for this Thread

Bookmarks

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