Register
Ask the Community

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

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

Status: Your support is needed Submitted by Contributor 2 weeks ago
1 Comment (1 New)

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

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.