how to truncate concatenated strings with a separator

elaine.novel

Hi guys,

Assuming I have a concatenated strings with different length but with the same separator..

To illustrate: I have company code+ledger+asset code but with "-" in between.. This 3 do not have the same length..

Assuming here are my data

1. 111-123456-12456

2. 1111-1234-124

3. 11-12345-234

How can i get the mid character using the mid formula.. What i did was this formula:

MID(Concatenated string, FIND("-", Concatenated string), LENGTH(Concatenated string))

But i am not getting the mid characters only.

Appreciate your help!

Thanks,

Elaine

Accepted answers

DavidSmith

@elaine.novel

As per Planual 2.08-18

https://community.anaplan.com/t5/Best-Practices/The-Planual/ta-p/49773

It is best to break the formulas up and keep the component parts in separate line items

I have broken then all up for an example, but you could combine some together if you were short of space

I hope that helps

David

All comments

VIGNESH.M

Hi @elaine.novel ,

Try this Formula,

MID(Concatenated string, FIND("-", Concatenated string) + 1, FIND("-", Concatenated string, FIND("-", Concatenated string) + 1) - FIND("-", Concatenated string) - 1)

It is best to divide the formula to small ones and use them.

Example: Find out "1st - Place" and "2nd - Place"

Regards,

Vignesh M

DavidSmith

@elaine.novel

As per Planual 2.08-18

https://community.anaplan.com/t5/Best-Practices/The-Planual/ta-p/49773

It is best to break the formulas up and keep the component parts in separate line items

I have broken then all up for an example, but you could combine some together if you were short of space

I hope that helps

David

elaine.novel

Hi David, was able to come up to 1 formula using the steps you sent. Thanks! 🙂

elaine.novel

Hi Vignesh,

Thank you for taking time to respond to my inquiry. I did the formula but I think the Length formula is needed to complete the formula. But I sincerely appreciate your answer. 🙂

Thanks again, Elaine

Quick Links

Can you help?

Unanswered questions

Importing Ragged Hierarchies
Hello - I have a 2 level ragged hierarchy (yes, it's a chart of accounts), that for reporting purposes is used in 4 different models (yes, it's a P&L). What is the best way set this to allow one to be the "source of truth" and then import between the four models without creating actions that include ignores? It seems like…
Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…