Solved: truncate string and onward

[mikeo1313]

I'd like to find "this text" and delete that string plus anything that follows it from a selection of cells in excel.

I did a couple of searches but to no avail.

thanks

[GTO]

Try:
[vba]
Selection.Replace What:="this text", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=False
[/vba]

Hope that helps,

Mark

[mdmackillop]

[VBA]
Sub DoReplace()
Dim cel As Range
For Each cel In Selection
If InStr(1, cel, "this text") > 0 Then
cel.Value = Left(cel, InStr(1, cel, "this text") - 1)
End If
Next
End Sub
[/VBA]

[mikeo1313]

Try:
[vba]
Selection.Replace What:="this text", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=False
[/vba]

Hope that helps,

Mark

This actually finds and erases "this text" but everything after it remains.

[mikeo1313]

[vba]
Sub DoReplace()
Dim cel As Range
For Each cel In Selection
If InStr(1, cel, "this text") > 0 Then
cel.Value = Left(cel, InStr(1, cel, "this text") - 1)
End If
Next
End Sub
[/vba]

I wonder if a parentheses is missing here on the cel.value line since it didn't work.

[Trebor76]

Hi mikeo1313,

Assuming there's a space after "this text" in the cell i.e. "this text " (remove the extra space from the following if not) try:

Sub DoReplace()
    
    Dim cel As Range
    
    For Each cel In Selection
        If InStr(1, cel, "this text") > 0 Then
            cel.Value = Replace(cel, "this text ", vbNullString)
        End If
    Next
    
End Sub

HTH

Robert

[mikeo1313]

To clarify here is an example of desired output:

sample :
vba soup, this text must go plus any random characters that follow from selection of cells

result :
vba soup,

[mikeo1313]

visual basic code from this link supposedly does it, here is a link JIK it helps though I guess vba & VB are totally different.

http://www.freevbcode.com/showcode.asp?id=2832

[Paul_Hossler]

Small change to Mark's suggestion: the asterisk after the search text


[VBA]

Selection.Replace What:="this text*", Replacement:=vbNullString, LookAt:=xlPart, MatchCase:=False

[/VBA]

Paul

[GTO]

Oopsie!

Thanks Paul :-)

[mdmackillop]

I wonder if a parentheses is missing here on the cel.value line since it didn't work.

Try deleting a bracket and see what happens. Can you explain how you implemented the code, as I'm mystified.

It works for me returning

vba soup,
vba soup,
vba soup,
and so on

[GTO]

I wonder if a parentheses is missing here on the cel.value line since it didn't work.

A guess, but by working, if you do not mean it errored, but that it just didn't replace/strip text... might your actual strings be in upper/mixed case?

[mikeo1313]

Try deleting a bracket and see what happens. Can you explain how you implemented the code, as I'm mystified.

It works for me returning

vba soup,
vba soup,
vba soup,
and so on

I can't explain for the life of me why it worked today and not yesterday, as I just deleted and put the same code today, tried it again and worked.

Maybe my machine had the vba flu + it wasn't an issue with case either.

Thank you all, both methods do work greatly