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

Options
u06gr8
u06gr8 Member, ALL USERS, Partner, Certified Model Builder Posts: 5 Contributor
in Planning & Modeling

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.

Tagged:

Comments

  • ujourney
    ujourney Member, ALL USERS, Community Member Posts: 5 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.

Categories