locked
Problem with the Search function RRS feed

  • Question

  • I Need to calculate the penultimate element of the path. It meens US200_LT03 in the below example

    Path

    TH001_LO01>SGG01_LT00>US200_LT03>TH003>SG010>TH001_LO01>SGG01_LT00>US200_LT03>USG20_LC10

    Each element are almost defined on a 10 ASCII character length.

    I tried to use the search function, knowing that there is an occurrence field indicating the number of element in the path, therefore, 8 in this case.

    As I need to search the “>” position from  the end of the path I used the following formula :

    =Search(“>”,[Path],Len(Path)-11)

    Ideally I should use a search on 11 and an other on 22 to detect the corresponding “>”, but the formula bugs as soon as the fix value become over 13.

    It is impossible too, to use the function with a parameter.

    Have an idea ?

    Wednesday, March 25, 2015 2:06 PM

Answers

  • Hi Charbonnier,

    You can adapt the approach I describe here but instead of using PATHITEM you can use PATHITEMREVERSE:

    =PATHITEMREVERSE(SUBSTITUTE(Table1[Path], ">", "|"), 2)

    This should always get the second to last path starting from right to left which in effect will return the last 'element path' according to the description that you provided.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, March 27, 2015 10:00 AM

All replies

  • Hi Charbonnier,

    According to your description, you need to calculate the penultimate element of the path, right?

    In your scenario, if the last and penultimate elements are defined on a 10 ASCII character length, then you can use the MID function which returns a string of characters from the middle of a text string, given a starting position and length.
    =MID(case0326[Parth],LEN(case0326[Parth])-20,10)

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, March 26, 2015 8:49 AM
  • This is exactly what I tried to do, But as data are not always so clean, elements have some times a different length.

    This is the reason of my search function to be sure of the parameter of the Mid function.

    And the Search function bugs.

     
    Friday, March 27, 2015 9:25 AM
  • Hi Charbonnier,

    You can adapt the approach I describe here but instead of using PATHITEM you can use PATHITEMREVERSE:

    =PATHITEMREVERSE(SUBSTITUTE(Table1[Path], ">", "|"), 2)

    This should always get the second to last path starting from right to left which in effect will return the last 'element path' according to the description that you provided.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, March 27, 2015 10:00 AM
  • Unbelievable !

    I definitivelly forgive the search way that bugs !

    Thanks a lot Michael !

    Friday, March 27, 2015 12:45 PM
  • Glad it helped :)

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Friday, March 27, 2015 1:14 PM