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))
Else
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
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.
vba and excel-vba tags indicates VBA, plus the fact that your approach is dangerous, and it doesn't address the fact that the text before the word "They" may change. It misses the point of the question, and doesn't safely answer it.
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