×
Create a new article
Write your page title here:
We currently have 3,187 articles on s23. Type your article name above or create one of the articles listed here!



    s23
    3,187Articles

    a collection of "forulars" i have used in Excel


    Capture the last segment[edit]

    The first one is what I found on [Mr Excel]

    The second is what I wanted =)

    Very nice logic I thourght.


    =REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),"") - extracts the last 2 space seperated segments - ie "moo foo" from "who are moo foo"


    =REPLACE(H12,1,SEARCH("@",SUBSTITUTE(H12,"-","@",LEN(H12)-LEN(SUBSTITUTE(H12,"-","")))),"") - extracts the last minus seperated segment - ie "foo" from "who-are-moo-foo"



    Quick dirt explanation (using above example)

    [1]

    LEN(H12)-LEN(SUBSTITUTE(H12,"-",""))

    "Length of the target cell" - "Length of the target cell with out the seperator"

    Len "who-are-moo-foo" = 15 - len "whoaremoofoo" = 12

    = 3

    [2]

    SUBSTITUTE(H12,"-","@",[1])

    Sub the [1]th "@" for a "-" in target cell.

    Substitute the 3rd - in "who-are-moo-foo" with @

    = "who-are-moo@foo"

    [3]

    REPLACE(H12,1,SEARCH("@",[2]),"")

    Replace from char 1 to the "@" for string "who-are-moo@foo"

    = "foo"



    Cookies help us deliver our services. By using our services, you agree to our use of cookies.
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.