Excel VBA - delete string content up to *word*

17
votes

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!

4
stringvbaexcel
Look I don't know why people would award a negative rating for this; I have literally no idea how to programme this and I genuinely need help. Why is it bad if I can't produce any code for this as someone who is fairly new to VBA? It's a bit unfair towards people who don't have as much experience as most users here. – jcv
@jcv I have been banned from asking questions because people downvoted without giving a reason or an answer... I know your pain – Babar
@pnuts well you may be right since most of my questions were not having code in it(so wrong style of question asking) but a comment on why someone is displeased might set person in the right direction. For instance, this question: telling him 'post what you tried so far' would be better than a downvote which does not help him how to make it better. – Babar

4 Answers

17
votes

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)
3
votes

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))
    Else
      strOut = "Error Empty Parameter in function call was encountered."
    End If
    DropTextBefore = strOut
End Function
1
votes

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

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

in the Find box. The 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 the same cell.

1
votes

This worked great for me:

Sub remove_until()

Dim i, lrowA, remChar As Long
Dim mString As String

lrowA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lrowA

mString = Cells(i, 1).Value


    If InStr(mString, "They") > 0 Then

    remChar = Len(mString) - InStr(mString, "They") + 1

    Cells(i, 2).Value = Left(mString, Len(mString) - remChar)

    End If


Next

End Sub