Replace text in a cell with criteria

[gtd526]

Hello,
Using VBA:
Im trying to remove text from a cell if the last 4 digits are " - x" or " - y" or " - z". Any of the 3 criteria's.

NBA.xlsm
	T	U	V	W
1	Eastern	W	L	Pct
2	Milwaukee - y	54	14	.794
3	Toronto - x	49	18	.731
4	Boston - x	44	23	.657
5	Miami - z	43	25	.632
6	Indiana - z	42	26	.618
7	Philadelphia	41	27	.603
8	Brooklyn	32	35	.478
NBA Standings-Yahoo

The code I created:
Dim c As Range

For Each c In Range("T:T")
If c.Value = Right(c, 4) = " - y" Then
c = Left(c, Len(c) - 4)
End If
MsgBox c.Address 'just to see if its working 'c'
Next c

I've tried putting " " for the spaces, among other changes, but its not working.
There are formula's that work, but I want VBA to do it for me (all at once).
Thank you.

 

[VBE313]

Function ExtractNthWord(x As String, y As Integer) 'GOT THIS FROM https://www.exceltip.com/tips/how-to-extract-nth-word-from-text-string-using-vba-in-microsoft-excel-2010.html
Dim word() As String, wordCount As Long
word = VBA.Split(x, " ")
wordCount = UBound(word)
    If wordCount < 1 Or (y - 1) > wordCount Or y < 0 Then
        ExtractNthWord = ""
    Else
        ExtractNthWord = word(y - 1)
    End If
End Function
Sub gtd546()
Dim cl As Object, lastRow As Long
With Sheets("NBA Standings-Yahoo")
    lastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
    For Each cl In .Range("T2:T" & lastRow)
        If ExtractNthWord(cl.Value, 1) <> "" Then
            cl.Value = ExtractNthWord(cl.Value, 1)
        End If
    Next cl
End With
End Sub

 

[mumps]

Try:

Sub ReplaceString()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("T2:T" & LastRow)
        If InStr(rng, "-") > 0 Then
            rng = Mid(rng, 1, WorksheetFunction.Find("-", rng) - 1)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Assuming x, y or z are the only text that can follow the space/dash/space, then give this macro a try...

Sub DeleteDashedText()
  Columns("T").Replace " - *", "", xlPart, , False, , False, False
End Sub

 

[gtd526]

Assuming x, y or z are the only text that can follow the space/dash/space, then give this macro a try...

Sub DeleteDashedText()
  Columns("T").Replace " - *", "", xlPart, , False, , False, False
End Sub

Thank you. Love the one-liners.

 

[gtd526]

Try:

Sub ReplaceString()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("T2:T" & LastRow)
        If InStr(rng, "-") > 0 Then
            rng = Mid(rng, 1, WorksheetFunction.Find("-", rng) - 1)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Thank you for your reply and macro. I only had to adjust -1 to -2, because it was leaving 1 extra space and the end of the value,
so my vlookup was not working.

 

[mumps]

Glad to help.