Calculating a parent WBS using a text column

[VSAT Ryan]

I am using a text field to enter WBS's, rather than Project's automated
feature. We are using the 1.2.3 format. I would like to have another text
column be the 'Parent WBS' column, but instead of actually typing in the
parent WBS, I would like to create a formula that does it for me.

My original approach would be the parent WBS would have a length of 2 less
than the WBS text field (WBS = 1.2.3, Parent WBS = 1.2). However, if I have
a WBS like '1.2.3.14', my output is '1.2.3.', rather than what I need it to
be - '1.2.3'.

Can anyone either help me with my approach, or provide a 'work around'?
Potential work around would be if there is a '.' at the end of the string,
then to remove it, but that sounds complicated, but may be the only way to do
it. I am also not that good with VBA. Thanks.

 

[John]

I am using a text field to enter WBS's, rather than Project's automated
feature. We are using the 1.2.3 format. I would like to have another text
column be the 'Parent WBS' column, but instead of actually typing in the
parent WBS, I would like to create a formula that does it for me.

My original approach would be the parent WBS would have a length of 2 less
than the WBS text field (WBS = 1.2.3, Parent WBS = 1.2). However, if I have
a WBS like '1.2.3.14', my output is '1.2.3.', rather than what I need it to
be - '1.2.3'.

Can anyone either help me with my approach, or provide a 'work around'?
Potential work around would be if there is a '.' at the end of the string,
then to remove it, but that sounds complicated, but may be the only way to do
it. I am also not that good with VBA. Thanks.

VSAT Ryan,
If I were doing this I would use VBA simply because when formulas start
to get complex (like this one) I find it difficult to keep track of the
total syntax structure. Plus, VBA has a function that formulas do not,
namely the InstrRev function - and that would make this a lot simpler.
Nonetheless, this formula should do what you need (as long as there is
only a maximum of 2 digits in the last place of the WBS).

Text2=IIf(instr(1,right([Text1],3),".")=2,mid([Text1],1,len([Text1])-2),m
id([Text1],1,len([Text1])-3))

Note: this assumes your WBS is entered in Text1 and your "parent" WBS is
in text2.

Hope this helps.
John
Project MVP

 

[VSAT Ryan]

I just tried this and it seemed to work:
Text3 = parent
Text4 = WBS

IIf(right(left([Text4],len([Text4])-2),1)=".",left([Text4],len([Text4])-3),left([Text4],len([Text4])-2))