Extract the text to the right of the right most period in a string

[wwalkerbout]

Hi,

I need to extract from a field, the right most strng from a period delimited field (dot notation), without using the InsStrRev() Function, which is not available in the environment in which I am working.

For example: First String Part.Second String Part.Third String Part.Fourth String Part

I need the string "Fourth String Part" portion of this overall string returned by my string manipulation function, but without the benefit of InstrRev(). I think it can be done with a combination of Right() and perhaps Len(), but I'm not sure how to go about it.

Any assistance would be very much appreciated.

Cheers,

Wayne

[June7]

I know I've helped a poster where InStrRev was not available but can't remember why. Is it web database? Is VBA not possible?

Let x represent your field then a single expression would be:

Mid(Mid(Mid(x,InStr(x,".")+1), InStr(Mid(x,InStr(x,".")+1),".")+1),InStr(Mid(Mid( x,InStr(x,".")+1), InStr(Mid(x,InStr(x,".")+1),".")+1),".")+1)

Alternative is to progressively extract the string with multiple calculations in query or textboxes on form/report.

Calc1: Mid(x,InStr(x,".")+1)

Calc2: Mid([Calc1],InStr([Calc1],".")+1)

Calc3: Mid([Calc2],InStr([Calc2],".")+1)

[wwalkerbout]

Hi June7,

More than likely, it was me who had the issue with InstrRev not being available, that you remember. This is in a Microsoft Project Server 2010 environment where there are limited Functions made available to us. At that time, I was able to split out each of the components of the dot notation string into separate fields and use your string manipulation on them separately. Thank you for your help with that first issue. It works great!

Now, after having that previous solution working so well for a number of months, my client has a new need. That is, they need to display the last value of this dot notation string. The overall string can be up to 8 levels deep (e.g. test1.text2.text3.text4.text5.text6.text7.text8). but it also can be 8, 7, 6, etc... levels deep. So the need now is to identify the last period (.) in the overall string and extract the characters to the right of that period.

Will what you have listed always select the string to the right of the last period? Or will it do that only for any string with 4 dot notation separated values? I haven't tested it, but it looks to be structured for four separated values.

With my previous solution, I have the values separated into the 8 fields I setup (named Level 1 to Level 8). Perhaps I should, instead of attempting to interrogate the dot notation string, try and work out which Level field (Level 1 to 8), is the last one populated and just grab that value. But, my initial thought was that there might be an easy way to just grab the last value from a dot notation string (without the use of InstrRev).

Thanks for your prompt response to my posting. Any additional feedback and recommendations you might have would be very much appreciated.

Cheers,

Wayne

[June7]

The all in one expression will only work for 4 part strings and all 4 have to be there. Would have to use the progressive calcs with some modification to handle the variability. Or use VBA (so easy).

But not understanding. If you already have the values in 8 fields, why this need to parse a string?

[wwalkerbout]

I didn't think I had an easy way to check across the multiple fields for the last field that has a value. But now that I think about it, I can check from Level 8 back the other way and as soon as the field is not null, then I can grab that value. Again, you've prompted me to think laterally and I think this might work.

As a matter of interest, is there a way of using the Right() Function in conjunction with the Len() Function to count the number of characters that are located to the right of the last period in the string? Just a thought.

~Wayne

[June7]

Think need InStrRev for that.

[orange]

Here is a small function that will find the position of the last "." in a field. I just knew it would come in handy one day...

Function fWhereIsIt(ByVal strInString As String, Optional SrchChar As String = ".") As Integer
'JED
 
'June 2001 modified
'Added optional parameter - character to search for
'needed a function to find the "\" in a directory...path

'JED Jan 2000 original
'
'Purpose:
'The function returns the position of the last "." in a string as is stands
'
'It could be generalized by allowing entry of the character to be searched
' ie "." or "?" or "*" etc.
'
'Usage: parsing a FirstName Initial LastName (Contact field) to determine where
'the initials , if there is/are "." embedded - this gives a position reference
'to split\extract LastName.  Other uses are possible

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, hiPos As Long, strTmp As String



    lngLen = Len(strInString)
    hiPos = 0
     'count the number of "." (asc 46) in string
     'count the number of " " (asc 32) in a string
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        
        'If Asc(strTmp) = 46 Then
        If Asc(strTmp) = Asc(SrchChar) Then
          hiPos = i
        Else
        End If
    Next i
    
    fWhereIsIt = hiPos
    
End Function

Here is a small test routine (It returned 79 for this test).

Sub testfind()
Dim str As String
Dim i As Integer
str = "Testtext1.Testingfor Text2. Testing for textstring3.testanother.TestnextToLast.Textlast"
Debug.Print str
i = fwhereIsit(str)
Debug.Print i
i = i + 1
Debug.Print i & "  " & str
Debug.Print Mid(str, i)
Debug.Print Mid(str, fwhereIsit(str) + 1)
End Sub

Here is the output:
Testtext1.Testingfor Text2. Testing for textstring3.testanother.TestnextToLast.Textlast
79
80 Testtext1.Testingfor Text2. Testing for textstring3.testanother.TestnextToLast.Textlast
Textlast
Textlast

So Mid(s,fwhereisit(s)+1) should print out the test following the last "."

Good luck.

[wwalkerbout]

Wow, this is great!!! I'm just trying to find out now from a colleague if it is possible to call a custom Function from within this very restrictive web front end (really, it s SharePoint infrastructure), on which Microsoft Project Server 2010 is built. If it can, I'll certainly use this. So thanks for providing it.

I'll let you know the outcome, once I find out the possibilities.

~Wayne

[wwalkerbout]

Hi Orange, June7,

Orange: As it turns out, my environment doesn't allow me to call a custom Function, so I wasn't able to use the process you were able to provide me. But thanks anyway.

June7: Your feedback got me thinking in a different direction again and helped me find a solution to my need.

What I was able to do is to use the Switch() Function to evaluate each of the separate fields that you previously helped me to generate, from the lower level fields (e.g. Level 8, Level 7, Level 6, tec...), back until I found a value. Then I grabbed that value. The Function I used was:

Switch([Level 8] <> "", [Level 8], [Level 7] <> "", [Level 7], [Level 6] <> "", [Level 6], [Level 5] <> "", [Level 5], [Level 4] <> "", [Level 4], [Level 3] <> "", [Level 3], [Level 2] <> "", [Level 2], [Level 1] <> "", [Level 1])

Thanks very much for your help!

Cheers,

Wayne