Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find second character (Excel 2003)

    I can use FIND to locate the position of my first instance of a character "-" for instance, but now I need to find the second time it appears in a text field. How would I do that? Can it be generalized to the nth time? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find second character (Excel 2003)

    For the second occurrence of a character, you can use a formula such as

    =FIND(B1,A1,FIND(B1,A1)+1)

    where A1 is the cell to search and B1 contains the character. This can be expanded to return the position of the third occurrence, but it quickly becomes unwieldy. If you really need to find the nth occurrence, it would be better to write a user-defined VBA function.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find second character (Excel 2003)

    For the nth occurence it can be done with a formula like:

    =FIND(CHAR(1),SUBSTITUTE(A1,B1, CHAR(1),C1))

    where A1 is the cell to search and B1 contains the character, and C1 contains the occurrence

    Steve

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find second character (Excel 2003)

    Thanks for the help! I can use both formulas.

Posting Permissions

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