Excel VBA - delete string content up to *word*

I'm trying to delete string content up to a certain word contained within the string. For example

"Emily has wild flowers. They are red and blue."

I'd like to use VBA in order to replace that with

"They are red and blue."

i.e. remove all the content up to the word "They". I don't know the string content and the number of characters contained in it.

I'm not sure how to do this and I'd really appreciate your help!


Here you go:

Dim s As String
s = "Emily has wild flowers. They are red and blue."

Dim indexOfThey As Integer

indexOfThey = InStr(1, s, "They")

Dim finalString As String
finalString = Right(s, Len(s) - indexOfThey + 1)

Simple example of dropping all text before value in string.

Sub Foo()
    Dim strOrig As String
    Dim strReplace As String
    strOrig = "The Quick brown fox jumped over the lazy dogs"
    strReplace = "jumped"

    MsgBox (DropTextBefore(strOrig, strReplace))

End Sub

Public Function DropTextBefore(strOrigin As String, strFind As String)
    Dim strOut As String
    Dim intFindPosition As Integer
    'case insensitive search
    'could made it so that case sensitivity is a parameter but this gets the idea across.
    If strOrigin <> "" And strFind <> "" Then
        intFindPosition = InStr(UCase(strOrigin), UCase(strFind))
        strOut = Right(strOrigin, Len(strOrigin) - (intFindPosition + Len(strFind) - 1))
      strOut = "Error Empty Parameter in function call was encountered."
    End If
    DropTextBefore = strOut
End Function

If the word is fixed, like "They" in the above example, you can simply do

CTRL + H (Replace) 
*They (your word with a star) 

in find box

Star * is a wildcard character which can be called as - of anything before or after(if added at end) the word.....

Cautious:- Take care when you have duplicate words in same cell...

Need Your Help

how to add a jcheckbox when the table model initially is not defined.

java database swing jtable jcheckbox

how to add a jcheckbox when the table model initially is not defined.

Clientside URL Routing

javascript html5 url-routing

Looking at turning my ASP MVC app into pure JS/Html however its not just a 1 page app, it has a couple of pages, but each one has alot of ajax and events.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.