Results 1 to 10 of 10
Thread: Excel Text Formula (Excel XP)

20050429, 02:24 #1
 Join Date
 Sep 2004
 Location
 Sacramento, California, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Text Formula (Excel XP)
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

20050429, 06:08 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Excel Text Formula (Excel XP)
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
CheersCheers,
Paul Edstein
[MS MVP  Word]

20050429, 07:20 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Text Formula (Excel XP)
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

20050429, 10:04 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Text Formula (Excel XP)
Here is a nonmacro 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

20050429, 14:23 #5
 Join Date
 Sep 2004
 Location
 Sacramento, California, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Text Formula (Excel XP)
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

20050429, 14:24 #6
 Join Date
 Sep 2004
 Location
 Sacramento, California, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Text Formula (Excel XP)
This gave a great alternative solution. By copying and pasting the data from Project into Excel this function worked great.
Thank you,
Carla

20050429, 14:28 #7
 Join Date
 Sep 2004
 Location
 Sacramento, California, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Text Formula (Excel XP)
(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

20050429, 14:30 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel Text Formula (Excel XP)
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.

20050429, 15:27 #9
 Join Date
 Sep 2004
 Location
 Sacramento, California, USA
 Posts
 432
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Text Formula (Excel XP)
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

20050430, 05:14 #10
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 Posts
Re: Excel Text Formula (Excel XP)
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.Cheers,
Paul Edstein
[MS MVP  Word]