Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: A formula to search backwards through a string

  1. #1
    Join Date
    23rd December 2004
    Posts
    21

    A formula to search backwards through a string

    I have a DOS directory ("D:\xxx\yyy\zzz\aaa\bbb") and want to find the piece after the last "\" (i.e. the lowest directory).

    Without resorting to macros or iterative searches, is there any way of searching for the last "\" from the end of the value and returning the "bbb". The number of levels (and therefore the number of "\" signs) is variable from one cell to another...



    Dan.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310

    Re: A formula to search backwards through a string

    Hi,

    I'm sure there is a neater way but for now try this formula.
    VB:
    =MID(A1,FIND("^",SUBSTITUTE(A1,"\","^",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))+1,LEN(A1)) 
    
    
    It replaces the last occurance of \ with the ^ character. It can then use this to determine where the mid function should extract the text from.

    Cheers
    Andy


  3. #3
    Join Date
    6th April 2005
    Posts
    15

    Re: A formula to search backwards through a string

    I was in exactly the same position as danosirra - and armed with a cunning plan or two I came up with some neat-ish VBA to solve the problem...

    VB:
     'find last occurrence of "\" in filename
     
    strlen = Len(Filename) 'find the length of the string "Filename"
    For count = 1 To strlen 'loop through the string
         
        If Mid(Filename, count, 1) = "\" Then 
            strcount = count 'overwrite each position of "\" into the variable strcount
        Else 
        End If 
         
    Next 
    
    
    Hope this helps
    Last edited by McScrew; December 14th, 2005 at 19:51.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th June 2005
    Location
    Lincolnshire, UK
    Posts
    1,137

    Re: A formula to search backwards through a string

    If I was to approach from a VBA point of view I would simply use the instrrev function
    Rich

    www.imits.co.uk - for all your training and development needs!

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,366

    Re: A formula to search backwards through a string

    Hi,

    Or..

    =REPLACE(A1,1,LOOKUP(9.99E+307,FIND("\",A1,ROW($1:$1024))),"")

    HTH

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Search For String And Print
    By Username99 in forum Excel and/or Word Help
    Replies: 50
    Last Post: October 10th, 2006, 18:26
  2. Search string and save a part of string
    By wmw99a in forum EXCEL HELP
    Replies: 1
    Last Post: February 24th, 2006, 08:26
  3. SEARCH for string
    By VICTORCHANG in forum EXCEL HELP
    Replies: 4
    Last Post: August 4th, 2005, 00:59
  4. String Search
    By malay_sarkar in forum EXCEL HELP
    Replies: 11
    Last Post: January 24th, 2005, 03:36
  5. search in a string
    By ljoseph in forum EXCEL HELP
    Replies: 2
    Last Post: December 10th, 2004, 05:53

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
  •  
porno