Deleting Particular Words based on Criteria

[madhuchelliah]

Hello all, i have multiple words in cell C4. Words are combination of alpha, numeric and symbols. Among the multiple words, the code has to find the word "FOR" and delete it along with the word before to the FOR. Please go through the example for better clarity. Thank you.

Value in cell C4 = XXX$%, AD024 FOR fds^&bjb ZZZZ RRRR, SSS-SSS, 58-2457R1 FOR asdsd , asdd,dsda

Expected Result= XXX$%, fds^&bjb ZZZZ RRRR, SSS-SSS, asdsd , asdd,dsda

 

[NdNoviceHlp]

This seems to do it. HTH, Dave

Sub test()
Dim SplitIt As Variant, Cnt As Integer
Dim MyPos As Integer, Tstr As String, TempStr As String
SplitIt = Split(ActiveSheet.Range("C4"), ",")
For Cnt = LBound(SplitIt) To UBound(SplitIt)
If InStr(SplitIt(Cnt), "FOR") Then
MyPos = InStr(1, SplitIt(Cnt), "FOR", 1)
Tstr = Right(SplitIt(Cnt), Len(SplitIt(Cnt)) - (MyPos + 3))
TempStr = TempStr & Tstr & ","
Else
TempStr = TempStr & SplitIt(Cnt) & ","
End If
Next Cnt
MsgBox Left(TempStr, Len(TempStr) - 1)
End Sub

 

[madhuchelliah]

This seems to do it. HTH, Dave

Sub test()
Dim SplitIt As Variant, Cnt As Integer
Dim MyPos As Integer, Tstr As String, TempStr As String
SplitIt = Split(ActiveSheet.Range("C4"), ",")
For Cnt = LBound(SplitIt) To UBound(SplitIt)
If InStr(SplitIt(Cnt), "FOR") Then
MyPos = InStr(1, SplitIt(Cnt), "FOR", 1)
Tstr = Right(SplitIt(Cnt), Len(SplitIt(Cnt)) - (MyPos + 3))
TempStr = TempStr & Tstr & ","
Else
TempStr = TempStr & SplitIt(Cnt) & ","
End If
Next Cnt
MsgBox Left(TempStr, Len(TempStr) - 1)
End Sub

Hello Dave, it is working as expected. Thanks for your time. Stay safe.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave