substitute function


i have list with codes 

in a test module I am trying to replace last digits of code by +1 using substitute function but i am getting result-

A1B1_1     ->A2B2_2  ----incorrect

A1B1_2    ->A1B1_3 ------correct

A1B1_3     ->A1B1_4------correct

A1B2_1      ->A2B2_2  -----incorrect

A1B2_2      ->A1B3_3  -----incorrect

A1B2_3      ->A1B2_4------correct


first, i am extracting number after "_" then incrementing it by +1-> incremented value line item

substitute function--> substitute(code line item, logic to find  character after"_"  , incremented value line item)

please suggest  approach to address this ....


Best Answer

  • JaredDolich


    Great question!! Love a good puzzle. So here's an idea. Instead of using SUBSTITUTE, you can consider FIND.

    We'll find the delimiter, in this case the underscore, strip off the suffix, increment it by one, and replace it. FIND and LENGTH have an advantage of SUBSTITUTE because if your suffix is greater than 9 then you'll have an issue. Here's the blueprint and grid. Looks like we got it!



  • @JaredDolich 

    thanks for the solution,

    I would like to understand the erratic behaviour of the substitute function.

  • @swapnil_J 


    Not sure I would classify the logic behind the function to "erratic", but to understand further, we would need to see your exact logic in the blueprint as well as the results.



  • @swapnil_J 

    Its a fair callout and one you should be proud of trying. Substitute is used to replace all the values that equal a certain lookup. Try the method above, I think you'll be happy and it guarantees to work if the values go over 9.

  • @JaredDolich 

    Thanks for the clarification and for the solution as well.