Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 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

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Plutonium Lounger
    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 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

  5. #5
    4 Star Lounger
    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

  6. #6
    4 Star Lounger
    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

  7. #7
    4 Star Lounger
    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

  8. #8
    Plutonium Lounger
    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.

  9. #9
    4 Star Lounger
    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

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •