REPLACE Text Function

Occasional Contributor

REPLACE Text Function



 I know there is the work around for REPLACE function using the FIND, LEFT, RIGHT functions.

 But using these functions only solves the first occurrence of the text to find or it's needed  to apply the functions several times if you know how many times the replace text exists in the source text.


I find it quite difficult, unreadable and long formula to get the necessary result and does not solve the case if the replace text exists more then once (unknown times) in the source text.


Also I'd like to consider the case when the replace have to be launched for the same original text several times, something like:


replace(replace(SourceText, "#PARAMETER1#", CurrentElement.Property1),"#PARAMETER2#", CurrentElement.Property2) ...etc...


For example we have a case that we have to send e.mail templates with  5 parameters to be replaced and it's kind a messy formula to build.


Please help us and create a REPLACE function ! :)




Occasional Contributor

Re: REPLACE Text Function


Indeed there is no convinient solution in Anaplan. However there is some trick to fix:

1) Assume you have some list with text attribute which you want to handle

2) Create line item for handling this attribute and cross it with time scale :)

3) Now put original attribute on the 1st date in the time scale

4) Create another line item with formula find from text from the first date and you catch the 1st occurance

5) Now cut the previous texture basin on previous find result and find your text within this part. In parallel cummulate all findings and handlings in additional Line Item. With summary last non-black you get your result in summary time scale item (all periods or year, etc)


Agree it looks really complex, but it works. Hope Anaplan Co will add replace function soon! :)


Thank you and let me know if this solution is not clear - I can attach screen shot from test environment

Occasional Contributor

Re: REPLACE Text Function

Hi Ansar, 

Thanks for your idea to use another dimension to be able to do more iterations to replace a character in a text. I presume you choose the Time dimension because has PREVIOUS function. Very clever!!
This could be done also, using a dimension with numeric codes like 1, 2,3, 4 to be able to have the previous element transforming the code in a number and subtract 1.
But using time you have only up to 12 or however a maximum number of occurrences to replace the character and the formulas are always launched 12 times, even there are less occurrences in the text.
As you said, it's only a work-around for a very simple operation: replace a character with more than 1 occurrences. 
I'd still remain at the idea that the replace operations in a text should be solved by a function like REPLACE.
Anaplan, Please help us and create a REPLACE function ! :)