"Missing" argument from "SUBSTITUTE" formula equivalent vs Excel

Anaplan's "SUBSTITUTE" formula has three arguments whereas excel's has a fourth optional argument that would allow for much simpler identification of specific substrings within a string that varies in construct:

 

Anaplan = SUBSTITUTE(Text to search in, Text to find, Replacement text)

 

Excel = SUBSTITUTE(Text to search in, Text to find, Replacement text, [Instance to start from])

 

i.e.

 

Text line item =

 

TR_111_909_G

D_1_2_UYH_LO

 

SUBSTITUTE(text line item, "_", "|", 3)

 

---> TR_111_909_G ---> TR_111_909|G ---> G

---> D_1_2_UYH_LO ---> D_1_2_UYH|LO ---> LO

 

etc.

2
2 votes

New · Last Updated

Comments

  • Would love to be able to specify the occurrence of the string to be replaced in the "SUBSTITUTE" function like in excel.  Alternatively, it would be great to have the ability to specify the occurrence of a string using the "FIND" function similar to an INSTR query in order to parse text data.

Get Started with Idea Exchange


See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!