PDA

View Full Version : Solved: truncate string and onward



mikeo1313
05-10-2010, 02:53 PM
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. :dunno

thanks

GTO
05-10-2010, 03:17 PM
Try:

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


Hope that helps,

Mark

mdmackillop
05-10-2010, 03:38 PM
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

mikeo1313
05-10-2010, 04:52 PM
Try:

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


Hope that helps,

Mark


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

mikeo1313
05-10-2010, 04:57 PM
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


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

Trebor76
05-10-2010, 05:49 PM
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
05-10-2010, 05:49 PM
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
05-10-2010, 06:05 PM
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
05-10-2010, 06:13 PM
Small change to Mark's suggestion: the asterisk after the search text




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


Paul

GTO
05-10-2010, 11:24 PM
Oopsie!:o:

Thanks Paul :-)

mdmackillop
05-11-2010, 12:12 AM
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
05-11-2010, 04:16 AM
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
05-11-2010, 06:52 AM
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 :beerchug: