Trim a node name till special character "-"


Hi Team,


Just looking for formula to trim my node name till a special character "-".

'AccountL1- 234'

I want to get only AccountL1.





Best Answer

  • DavidSmith

    I would think the FIND formula would need to be


    FIND("-", 'Account L1'.Display Name, 0)




  • @usha.cherukuri 


    Assumption - Account L1 is your list, if not then tweak your second parameter


    First you will have to figure out the place of the " -" . Use this formula in Line item Test


    Test = FIND("-", NAME(ITEM('Account L1')), 0) 

    Second line item Test 1 = LEFT(NAME(ITEM('Account L1')),Test-1)


    Hope this helps


  • Hi Mishab,


    Thanks for your rsponse, but the first formula for test

    FIND("-", NAME(ITEM('Account L1')), 0) doesn't give any result and its a numbered list dimension.


    the formula worked but doesnt give any value here.




  • @usha.cherukuri,

    I hope it should be the code of that list. Please try Code(Item('')) instead Name(Item('')).

  • Hi Kavin,

    Yes even I thought so, but still no result:(

    It takes the formula in but result is 0 every time, though I have special characters in member name.

  • Could you please tell us how would you get the result 'AccountL1- 234'? If it is not code/name, then it should be the attribute of that list (Either property/line item). So apply your formula on that property/line item and you will get your end result. 

    For ex,

    I have a numbered list 'A' with Display Name as property(text). Then the formula should be,

    Test = FIND("-", 'A.Display name', 0) 

    Second line item Test 1 = LEFT('A.Display name',Test-1)

    Hope this helps.



  • Hi David,

    Thanks this now works for child node in AccountL1 dimension perfectly.


  • Hi David,

    This is working good for Child Level, but Parent Level for 'AccountL1' is 'AccountL0' which is not a number list, for that the value is showing zero.
    Is there any way how we can get the parent level also populated here?

  • The challenge you have here is the line item is dimensioned by Account L1 and in a composite hierarchy Account L0 is a calculation

    So we need to build a couple of extra line items

    1. You need to know if the item is a L1 or L0, so set up a Boolean line item called Child? and set this to TRUE

    2. Create another line item that brings in the Parent of the L1 - in your model it will be something like PARENT((ITEM('Account L1')).  It is always best to have this in a separate line item (You will likely use it again).  Remember "calculate once, reference many times" 

    3. Create a text line item for the name of A0, but set the summary option to 'Last non-blank'

    4. This is the existing line item we just solved for

    5. Now you bring it all together with a conditional and set this summary option to 'Formula'




  • @usha.cherukuri 


    Probably you might have to add two more line items. Here is how I achieved this.


    Added Test 3  Line item where I checked Parent Of the List, kept the Summary as Last Non Blank




    Added Line item Test 4 - Kept the summary as Formula






    One thing to note that you will have to change the Summary of the first line item as well - Change it to Formula