Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Using the "find" command excel 10

    I would like to find a specific character or space in a cell where it may be there multiple times. For instance, If a cell is populated with "sprt-ssed5-3f\ggt". How would I write my FIND formula to find the second "-"? What if it five "-" and I wanted to find the fouth one?

    Is this possible"?

    Thanks

  2. #2
    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
    You can't do it directly with FIND. FIND will find the first occurence, starting from a particular start point. But you would need to start looking after the nth -1 location. It can be done "indirectly", by first converting the second "-" to a character not in the string and then finding that character. I typically would use the first ASCII character which is not used much. So to find the 2nd "-" in cell A1:

    =FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))

    To find the 4th one, replace the 2 with a 4:
    =FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),4))

    Note that you will get a #value error if there is no 4th "-".

    Steve

Posting Permissions

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