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

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

1 Comment
ujourney
Contributor

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.