Trim Before text

[DMT Dave]

Hi Guy's i am trying to trim all text BEFORE specific words but keep everyting AFTER these words, the code I have got deletes the full string ???



Kindest

OldStr = InStr(Me.txtMailMessage, "Form Response Notification")
MyRight = Trim(Right(Me.txtMailMessage, Mystr))
NewStr = MyRight
Me.txtMailMessage = NewStr

[June7]

InStr() returns a number which is position of first character of substring. Trim just removes spaces.

So you do not want to retain the search words? There will always be preceding and following text?

x represents your data.

Trim(Mid(x, InStr(x, "Form Response Notification")+26))

[DMT Dave]

Hi June7, I will try and adapt your suggestion, yes basically delete everything before "Form Response Notification" and KEEP everything after ie:

me.txtMailMessage is:

This was sent to you, please do not print
This is also a message for you
Form Response Notification
My Name is Joe Bloggs
My Number is 123456

I am trying to delete:

This was sent to you, please do not print
This is also a message for you

New txtMailMessage is:

Form Response Notification
My Name is Joe Bloggs
My Number is 123456

[moke123]

if it is consistant that your removing "This was sent to you, please do not print This is also a message for you" you may also be able to use

replace(me.txtMailMessage,"This was sent to you, please do not print This is also a message for you","")

(you may have to include any carraige returns in the text to be replaced.)

[orange]

As moke123 said, if there is a consistent format to the incoming info, then address that up front.
In what format is the data received? (csv, text file???)
There are definitions and samples of Access functions here.

[accesstos]

Looks like the Mid() function is designed for your issue.
With assist of InStr() function, returns exactly that you want.

You can use them in your own function as seems in sample code bellow:

Function StringRight(strText As String, strStartFrom As String, Optional fTrimStartText As Boolean = False) As String
    'Returns the right part of a text starting from the strStartFrom text.
    'Keeps the strStartFrom with the returned text if the third argument is False or is omited.
    Dim lngPos As Long

    lngPos = InStr(1, strText, strStartFrom)
    
    If lngPos > 0 Then
        If fTrimStartText Then lngPos = lngPos + Len(strStartFrom)
        StringRight = Trim(Mid(strText, lngPos))
    End If
End Function

Sub TestStringRight()
    Dim strOld As String
    Dim strStart As String

    strOld = "This was sent to you, please do not print" & vbCrLf _
             & "This is also a message for you" & vbCrLf _
             & "Form Response Notification" & vbCrLf _
             & "My Name is Joe Bloggs" & vbCrLf _
             & "My Number Is 123456" & vbCrLf

    strStart = "Form Response Notification"
    
    MsgBox StringRight(strOld, strStart)

    MsgBox StringRight(strOld, strStart, True)

    MsgBox StringRight(strOld, "My Name")
    
    MsgBox StringRight(strOld, "My Num")

    MsgBox StringRight(strOld, "My Number is", True)

End Sub

Good luck!

John

[June7]

So you do want to retain the search text.

Mid(x, InStr(x, "Form Response Notification"))

[DMT Dave]

Hi June7, and thanks to accesstos yes this does start the newstring from "Form Response Notification"

How do i stop before specific text so i am picking out text from "Form Response Notification" and stopping at "reply to Joe Bloggs"

Can I exclude "Form Response Notification" and pick text from after that To and exclude "Reply To Joe Bloggs"

So Bold text below is picked out

ie:

This is a message
It was sent from me
Form Response Notification
Hello
My name is: Joe Bloggs
My NUmber is 123456
Reply to Joe Bloggs
PLease do not print
Goodbye
Have a nice day

[June7]

Hello was not in the original example, nor were the additional lines starting with "Reply".

Now you show you DON'T want to retain "Form Response Notification" and also show more text at end you want to eliminate.

Consistency of structure is critical in string manipulation. Will Hello and Reply always be in the string?

Mid(Left(x, InStr(x, "Reply")-1), InStr(x, "Hello"))

[DMT Dave]

Hu June7, thank you, i am using words as examples rather than what is officially recieved but thank you for response

[orange]

Dave,
As mentioned earlier, please tell us the format of the incoming data and the context in which data is sent to you from somewhere for your manipulation/processing.
We see posts where people receive data from some other system or supplier in a text file format (.csv,.txt etc). Others get data from web sites or external sources; some from email...
You know the source of the data and the context and readers don't, so please provide a brief description of the process for receiving your data. At the moment the details are unclear and changing as June has said.
Good luck.

[DMT Dave]

Hi Orange, its email grabbed into a text box txtMailMessage, this is set to long text in the table, so i am trying to grab data from "form response notification" up to "reply to customer" because in between these 2 sets of words there are data than can be distributed to other access fields by the click of a button, so i i can get rid of everything before and after that would make it easier, this is sent to us via an automated email system froma client so i can't really ask them to take out this for our database to respond easier without who sent it, what server sent it, etc etc..... kindest

[orange]

So is there any option for the client to add info/words/blank lines etc in the email that gets sent automatically?
Can you provide say 4 or 5 of these actual text responses for readers to see?

Are you getting these from Outlook or other?

[accesstos]

Well Dave, this is a multifunctional function specially designed for your needs but I hope useful to others:

Function StringPart(ByVal strText As String, _
                           Optional strFrom As String, _
                           Optional strTo As String, _
                           Optional ExFrom As Boolean = False, _
                           Optional ExTo As Boolean = False) As String
    'Returns the part of strText between strFrom and strTo strings.
    'If strFrom is omited, works as Left function.
    'If strTo is omited, works as Right function.
    'If both strFrom and strTo are passed, works as Mid function.
    'If ExFrom is True, exclude the strFrom of the returned value.
    'If ExTo is True, exclude the strTo of the returned value.
    
    Dim strMid As String
    Dim lngPos As Long
    Dim lngLen As Long

    strMid = strText

    'Right function.
    lngLen = Len(strFrom)
    If lngLen > 0 Then
        lngPos = InStr(1, strMid, strFrom)
        If lngPos > 0 Then
            If ExFrom Then lngPos = lngPos + lngLen + 1
            strMid = Trim$(Mid$(strMid, lngPos))
        End If
    End If
    'Left function.
    lngLen = Len(strTo)
    If lngLen > 0 Then
        lngPos = InStr(1, strMid, strTo)
        If lngPos > 0 Then
            If Not ExTo Then lngPos = lngPos + Len(strTo) + 1
            strMid = Trim$(Left$(strMid, lngPos - 1))
        End If
    End If
    StringPart = strMid
End Function

Run the procedure below and check the results.

Sub TestStringPart()
    Dim strOld As String
    Dim strStart As String
    Dim strStop As String

    strOld = "This was sent to you, please do not print" & vbCrLf _
             & "This is also a message for you" & vbCrLf _
             & "Form Response Notification" & vbCrLf _
             & "My Name is Joe Bloggs" & vbCrLf _
             & "My Number Is 123456" & vbCrLf _
             & "Reply to Joe Bloggs" & vbCrLf _
             & "PLease do not print" & vbCrLf _
             & "Goodbye" & vbCrLf _
             & "Have a nice day"

    strStart = "Form Response Notification"
    strStop = "Reply to"

    MsgBox "strFrom = '" & strStart & "'" & vbCrLf & vbCrLf & StringPart(strOld, strStart), , "Right function"
    MsgBox "strFrom = '" & strStart & "'" & vbCrLf & "ExFrom=True" & vbCrLf & vbCrLf & StringPart(strOld, strStart, , True), , "Right function"
    MsgBox "strTo = '" & strStop & "'" & vbCrLf & vbCrLf & StringPart(strOld, , strStop), , "Left function"
    MsgBox "strTo = '" & strStop & "'" & vbCrLf & "ExTo=True" & vbCrLf & vbCrLf & StringPart(strOld, , strStop, , True), , "Left function"
    MsgBox "strFrom = '" & strStart & "'" & vbCrLf & "strTo = '" & strStop & "'" & vbCrLf & vbCrLf & StringPart(strOld, strStart, strStop), , "Mid function"
    MsgBox "strFrom = '" & strStart & "'" & vbCrLf & "strTo = '" & strStop & "'" & vbCrLf & "ExFrom=True" & vbCrLf & "ExTo=True" & vbCrLf & vbCrLf & StringPart(strOld, strStart, strStop, True, True), , "Mid function"
      
End Sub

Cheers,
John

[moke123]

its email grabbed into a text box txtMailMessage, this is set to long text in the table,

Is the email plain text or html? Rich text?

Just to be sure, I would check to see if your seeing everything in the string. For instance if its html or rich text, there may be formatting you cant see.
I usually use this simple function that debug.prints the ascii code of each character including the non-printable ones (ie. chr(10) & chr(13) or <div>)
It may save you a world of frustration.

Public Sub sChkNonPrint(strCheck)
    Dim lngLoop As Long


    For lngLoop = 1 To Len(strCheck)
        Debug.Print Mid(strCheck, lngLoop, 1) & ": " & Asc(Mid(strCheck, lngLoop, 1))
    Next lngLoop


End Sub

@Orange: I hope your not in W.P.B. with the storm coming. If you are, stay safe and could you go check my place in Jupiter next week?