substitute function

swapnil_J
Frequent Contributor

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 ....

1 ACCEPTED SOLUTION

Accepted Solutions
JaredDolich
Moderator

Re: substitute function

@swapnil_J 

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!

Substitute001.pngSubstitute002.png


Jared Dolich

View solution in original post

5 REPLIES 5
JaredDolich
Moderator

Re: substitute function

@swapnil_J 

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!

Substitute001.pngSubstitute002.png


Jared Dolich

View solution in original post

swapnil_J
Frequent Contributor

Re: substitute function

@JaredDolich 

thanks for the solution,

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

rob_marshall
Moderator

Re: 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.

 

Rob

JaredDolich
Moderator

Re: substitute function

@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.


Jared Dolich
swapnil_J
Frequent Contributor

Re: substitute function

@JaredDolich 

Thanks for the clarification and for the solution as well.