String formating question (1 Viewer)

[woknick]

I have a comment that I pulled with a dlookup from a table. Example below

Interface request received and filed (nk-11/14/2006) Test file sent to vendor. (nk-11/15/2006).

I want to seperate the data based on date. Example below

11/14/2006 Interface request received and filed
11/15/2006 Test file sent to vendor.

Is there an easy way to create a loop to walk through each character and format the original data to the formatted data?

thank you

 

[gemma-the-husky]

Try this

you will need to handle the split strings yourself, but this should split into
date, by, and messagetext

needs A2000 on, as instrrev not avaialble in A97

Sub parseit(mystring as string)
Dim pos As Long
Dim pos2 As Long
Dim pos3 As Long
Dim msgtext As String
Dim msgby As String
Dim msgdate As Date

pos = InStrRev(mystring, "(")
pos2 = InStrRev(mystring, "-")
pos3 = InStrRev(mystring, ")")
msgtext = Left(mystring, pos - 1) 'everything up to the final (
msgby = Mid(mystring, pos + 1, pos2 - (pos + 1)) 'everytyhing between the ( and the -
msgdate = DateValue(Mid(mystring, pos2 + 1, (pos3 - 1) - (pos2))) 'the date between the - and the )

MsgBox ("Date: " & msgdate & vbCrLf & _
"By: " & msgby & vbCrLf & _
"Message: " & msgtext)

End Sub

 

[raskew]

Hi -

The solution works well. For A97 users, here's a roll-your-own version of the InStrRev() function.

Function InStrRev97(ByVal pstr As String, pItem As String) As Integer
'*******************************************
'Purpose:   Return location of last instance of a character or phrase.
'Inputs:    ? InStrRev97("the quick brown fox jumped the lazy dog", "the")
'Output:    28 - Location of last occurence of "the"
'*******************************************

Dim i    As Integer
Dim n    As Integer
Dim tLen As Integer

    n = 0
    tLen = Len(pItem)
    For i = Len(RTrim(pstr)) To 1 Step -1
       If Mid(pstr, i, tLen) = pItem Then
           n = i
           Exit For
       End If
    Next i

    InStrRev97 = n

End Function

Bob

 

[woknick]

thanks for the help

Thanks for the help!! Code works great for pulling the groupings. Ill just have to figure out how to handle the spliting...