Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Can =search( ) or =find( ) work from right to left?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =search( ) or =find( ) work from left to right. Can I make it work from right to left?


  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    not directly, but you can get it done - describe what you're trying to accomplish.

    paddy

  3. #3
    New Member
    Join Date
    Nov 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've found that this method is pretty effective...

    If your cell is A1 then this should be the type of thing that you want to do...

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

    hi-there-Dr.

    P.S. The doctor is not in...

    9

  4. #4
    New Member
    Join Date
    Oct 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Eh? What's that?
    I find your formula rather inefficient and pedestrian.

    Perhaps Mr. Excel can teach you some math?

    You seggesting I substitute the duplicate char. with others so I can search by the uniqe but your formula does not address the QUANITY of the offending little buggers. Somtimes there are 3 dups and somtimes 5 or 6.

    Try another prescription.

  5. #5
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    Dear Dr,

    "I find your formula rather inefficient and pedestrian.

    Perhaps Mr. Excel can teach you some math"

    No need to be rude...if you're in a position to judge the suggestion so harshly, I'm surpised you don't also find yourself in a position to solve the problem.

    Again, care to give an example of what you are actually trying to accomplish.

    paddy

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-11-07 17:34, Doctor Logic wrote:
    Eh? What's that?
    I find your formula rather inefficient and pedestrian.

    Perhaps Mr. Excel can teach you some math?

    You seggesting I substitute the duplicate char. with others so I can search by the uniqe but your formula does not address the QUANITY of the offending little buggers. Somtimes there are 3 dups and somtimes 5 or 6.

    Try another prescription.
    To address you concern about the quantity,
    you could use
    =FIND("@",SUBSTITUTE(A1,"f","@",LEN(A1)-LEN(SUBSTITUTE(A1,"f",""))))
    to find the place of the last instance of f.
    If you are searching for longer than 1 character strings, this would have to be dressed up a bit.

    Another option, if memory serves, can be found by searching for key word reversetext for a UDF Aladin had posted.

    [ This Message was edited by: IML on 2002-11-07 18:21 ]

  7. #7
    New Member
    Join Date
    Oct 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Paddy,

    My comments were directed to Mr. EH?, whom I assure you he can well take care of himself. Don't be upset if I am rude to him; he sit's right next to me and likes to think he is smarter than all you good folks on the Mr. Excel site. I told him Paddy was smarter than him because Padday was able to answer many of my questions that Mr. EH? was not, so he was jelous.

    Again, don't worry about Mr. EH?. He is an annoying twit. (Unlike you, who have been very helpful many times.

    The Dr.

  8. #8
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,231
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    Dr,

    Fair enuogh, altohugh there is the general tone of messages to the board to consider, irrespective of whether any particular member takes offence...

    & is this solved or what? if not, post an example...

    paddy

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good stuff! Along IML's recommondation (UDF), you could paste the following in a normal module:


    Function RevInStr(findin As Range, tofind As String) As Integer
    ' Chris Rae's VBA Code Archive - http://chrisrae.com/vba
    Dim findcha As Integer
    For findcha = Len(findin) - Len(tofind) + 1 To 1 Step -1
    If Mid(findin, findcha, Len(tofind)) = tofind Then
    RevInStr = findcha
    Exit Function
    End If
    Next findcha
    ' Defaults to zero anyway (tsk, tsk, etc)
    End Function


    And use it as such:

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    test*this*hello1015
    2
    blah.sjdksa.sadjka.1919
    3
    blah.sjdksa.sadjka.019
    4
    dshkjfs-gh-hjh-j1516
    Sheet1

    [HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    _________________
    Cheers, Nate Oliver

    [ This Message was edited by: NateO on 2002-11-07 18:39 ]

  10. #10
    New Member
    Join Date
    Oct 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Capital piece of code from Nate. (Sure wish I knew how to write VB!)

    As for Paddy's example, here is the contents of a cell:

    WIRE THHN-10-TAN-19STR-CU-500S/R

    I would like to return everything after the last - . I can write nested serches for "-" as the silly Mr. EH? suggests except not all the cells have the same amount of "-" in them, so you nest the find for one incidence? two? five? Mr. EH? knows this so he is just being annoying. I want the formula to work on all the cells. If the formula found the FIRST incidence of "-" FROM THE RIGHT instead of the left, it would work perfect.

    I suppose I could write a big formula or use multiple ajacent columns to do it but I always try to get it all into ONE cell and (if at all possible) with a FUNCTION rather than a formula.

    I was just hoping.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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