Excel Text Formula (Excel XP)

[csimms]

I am looking for a formula that will look at a text string and identify the last period in a text string and truncate everything to the right of it. I am helping another lounger in the Project forum with this calculation and since Project uses many of the same text manipulation functions as Excel I thought I might try having all of the lounge Excel formula gurus take a look, then I can provide the how to apply in Project information, and of course credit to any contrubuting loungers.

Example:
1.1 = 1
1.2.1 = 1.2
1.1.1=1.1
1.2.12 = 1.2
1.2.2.3 = 1.2.2
I created: (the name of the field I am working with is WBS)
IIf(Len([WBS])>1,Left([WBS],(Len([WBS])-2)))
This works great until I get to 1.2.12 then it stops working because the logic I used in the funcion does not account for this possibility
Any Ideas,
Carla

[macropod]

Hi Carla,

Your code looks more like vba than an Excel formula. A possible vba solution is:

Sub FindPeriod()
Dim CharPos As Integer, WBS As String
WBS = InputBox("WBS")
For CharPos = Len(WBS) To 0 Step -1
On Error Resume Next 'In case WBS is empty
If Mid(WBS, CharPos, 1) = "." Then
MsgBox "Input Value: " & WBS & vbCrLf & " Result: " & Left(WBS, Len(WBS) - Len(Mid(WBS, CharPos)))
Exit For
End If
Next CharPos
End Sub

Cheers

[HansV]

You can also use the InstrRev function:

Function TruncLast(aValue)
Dim intPos As Integer
If aValue = "" Then
TruncLast = ""
Else
intPos = InStrRev(aValue, ".")
If intPos Then
TruncLast = Left(aValue, intPos - 1)
Else
TruncLast = aValue
End If
End If
End Function

[sdckapr]

Here is a non-macro solution:
=IF(ISNUMBER(FIND(".",A1)),LEFT(A1,(FIND(CHAR(1),S UBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))-1),A1)

If the text has no decimal, it will just give the text itself.

It works by counting the number of decimals and replacing the last one with an ASCII 1 (used since very unlikely it will be in the string). It finds the location of this character and takes everything to the left of it.
Steve

[csimms]

Hans,

As always I learn someting from posted solutions. I have not used the InstrRev function in Excel before so I got to learn sometihing new. Unfortunatly Project(the application that I was looking for a solution in) would not recognize that string function. Andrew Locton created a nice macro as a solution post 474722.
Carla

[csimms]

This gave a great alternative solution. By copying and pasting the data from Project into Excel this function worked great.
Thank you,
Carla

[csimms]

(Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>)

Thank you for taking a look and a VBA solution would have been a good answer. A solution was found by using a maco. See <post#=474722>post 474722</post#> from Andrew Locton for the solution.

Thank you,
Carla

[HansV]

I'm glad you have a solution. Please note that Andrew's WhosYourDaddy function is more or less equivalent to mine, it also uses InstrRev.

[csimms]

Hans,

You are correct. I took your function and converted it into an IIF function and tried to attach it to a custom field definition which was the original thought for a solution. Interestingly enough the ability to create custom functions for fields definitions is much more limited in Project than the functions recognized in VBA. I would have thought that the functions available to VBA would be the same as the ones available to build custom fields from, but apparently not. Probably bottom line is in most cases is is easier to use VBA in the alternative to the various builders that come with the application.
Building functions in Project is pretty much the same as in Excel or Access so I have sure received a lot of great input from this board.

Thank all of you for your help
Carla

[macropod]

Hi Carla,

FWIW, the solution I posted will work in all MS apps that support macros: Word, Excel, Powerpoint, Access, Project, and can easily be converted from a sub to a function.

Cheers
PS: The line "MsgBox "Input Value: " & WBS & vbCrLf & " Result: " & Left(WBS, Len(WBS) - Len(Mid(WBS, CharPos)))" should really have been put at the very end, just before the 'end sub' line.