## substitute function

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: substitute function

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!

Jared Dolich
5 REPLIES 5
Moderator

## Re: substitute function

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!

Jared Dolich
Frequent Contributor

## Re: substitute function

thanks for the solution,

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

Moderator

## Re: substitute function

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

Moderator

## Re: substitute function

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
Frequent Contributor

## Re: substitute function

Thanks for the clarification and for the solution as well.