how to truncate concatenated strings with a separator

elaine.novel
elaine.novel
in Modeling

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

Tagged:

Best Answer

Answers

  • VIGNESH.M
    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

  • elaine.novel
    elaine.novel

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

  • elaine.novel
    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

Categories