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!




Best Answer


  • 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"



    Vignesh M

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

  • 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