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
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
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
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.