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
-
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!
1
Answers
-
thanks for the solution,
I would like to understand the erratic behaviour of the substitute function.
0 -
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
0 -
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.
1 -
1
