# Problem with the Search function

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

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

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,

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