substitute function

swapnil_J

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

Accepted answers

JaredDolich

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

All comments

JaredDolich

@swapnil_J

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

swapnil_J

@JaredDolich

thanks for the solution,

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

rob_marshall

@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

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

swapnil_J

@JaredDolich

Thanks for the clarification and for the solution as well.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…