Sub String and Find String together

[Rajesh]

Hi there,

I am trying to extract specific value from the cell.

From the cell Col AL4       | | 0996792463_0037932 | S61584 | 101 | 440061339 |

Using the formula in excel =TRIM(MID(AL4,5,SEARCH("|",AL4,4)-5))

it gives me the correct value = 0996792463_0037932

However when I am trying Alteryx 

Substring([AL],5,FindString([AL],"|",4)-5 

it is not working.

Need help

[Rajesh]

Hi there,

I am trying to extract specific value from the cell.

From the cell Col AL4       | | 0996792463_0037932 | S61584 | 101 | 440061339 |

Using the formula in excel =TRIM(MID(AL4,5,SEARCH("|",AL4,4)-5))

it gives me the correct value = 0996792463_0037932

However when I am trying Alteryx 

Substring([AL],5,FindString([AL],"|",4)-5 

it is not working.

Need help

[binu_acs · Accepted solution]

@Rajesh The FindString() function will always check the first occurrence of the character in the given string, in this case, this function will return the first occurrence of the char "|", that's why you are not getting the correct result, one way of achieving your expected output you need to trim the characters || from the string then try with the formula

[Rajesh]

@binu_acs  Thank you so much this really works.