Highlighted
Occasional Contributor

how to truncate concatenated strings with a separator

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

4 REPLIES 4
Certified Master Anaplanner

Re: how to truncate concatenated strings with a separator

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

Community Boss

Re: how to truncate concatenated strings with a separator

@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

2019-10-09_07-24-22.png

 

2019-10-09_07-24-53.png

 I hope that helps

David

 

Occasional Contributor

Re: how to truncate concatenated strings with a separator

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

Occasional Contributor

Re: how to truncate concatenated strings with a separator

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