vba remove unwanted text

[gtd526]

Hello,
I want to remove unwanted text "(35-11)" from BF:BF. I have the following macro, but it doesn't work.
Thank you.

Sub Test()
    
    Sheets("TeamRankings").Select 'Teamrankings
    'vs Rank
        Application.ScreenUpdating = False
        lastRow = Cells.Find("(", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For Each Rng In Range("BF3:BF" & lastRow)
            If InStr(Rng, "(") > 0 Then
                Rng = Mid(Rng, 1, WorksheetFunction.Find("(", Rng) - 2) 'rid of extra space
            End If
        Next Rng
        Application.ScreenUpdating = True

End Sub

NBA.xlsm
	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
2		Rank	Team	Rating	v 1-5	v 6-10	v 11-16	Hi	Low	Last
3		1	Utah (35-11)	8.1	5-3	7-2	8-3	1	12	1
4		2	LA Clippers (32-17)	5.8	4-3	3-6	7-3	2	5	2
5		3	Milwaukee (29-17)	5.4	2-5	3-4	7-4	1	4	3
6		4	Phoenix (32-14)	4.8	3-3	7-1	9-2	4	14	4
7		5	Denver (29-18)	4.5	4-4	4-5	6-3	5	13	5
8		6	Philadelphia (32-15)	4.2	1-6	6-1	7-2	4	11	6
9		7	LA Lakers (30-17)	3.9	2-5	2-3	5-4	1	7	7
10		8	Brooklyn (32-15)	3.4	6-1	4-2	8-2	2	9	8
11		9	Dallas (24-21)	2	4-8	2-2	6-4	3	16	9
12		10	Boston (23-24)	1.7	5-4	0-6	8-5	6	13	10
TeamRankings

Cell Formulas
Range	Formula
BD2:BD12	BD2	=IF(BF:BF="Milwaukee","MIL",IF(BF:BF="LA Lakers","LAL",IF(BF:BF="Dallas","DAL",IF(BF:BF="Boston","BOS",IF(BF:BF="LA Clippers","LAC",IF(BF:BF="Toronto","TOR",IF(BF:BF="Utah","UTA",IF(BF:BF="Houston","HOU",IF(BF:BF="Denver","DEN",IF(BF:BF="Miami","MIA",IF(BF:BF="Okla City","OKC",IF(BF:BF="Philadelphia","PHI",IF(BF:BF="Indiana","IND",IF(BF:BF="New Orleans","NO",IF(BF:BF="San Antonio","SAN",IF(BF:BF="Brooklyn","BRK",IF(BF:BF="Portland","POR",IF(BF:BF="Phoenix","PHX",IF(BF:BF="Orlando","ORL",IF(BF:BF="Memphis","MEM",IF(BF:BF="Sacramento","SAC",IF(BF:BF="Minnesota","MIN",IF(BF:BF="Detroit","DET",IF(BF:BF="Chicago","CHI",IF(BF:BF="Washington","WAS",IF(BF:BF="New York","NYK",IF(BF:BF="Atlanta","ATL",IF(BF:BF="Charlotte","CHA",IF(BF:BF="Golden State","GS",IF(BF:BF="Cleveland","CLE",""))))))))))))))))))))))))))))))

 

[RoryA]

You could use the Find/Replace dialog for that. Select the data, then Ctrl+H, enter:

(*

(there's a space at the beginning) in the Find box, then leaving the Replace box empty, press Replace All.

 

[Fluff]

Try

                Rng.Value = Mid(Rng, 1, WorksheetFunction.Find("(", Rng) - 2) 'rid of extra space

 

[Joe4]

In addition to Fluff's needed change, you may also want to change this row:

lastRow = Cells.Find("(", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

to this:

LastRow = Cells(Rows.Count, "BF").End(xlUp).Row

so it runs specifically against column BF.

 

[gtd526]

Try

                Rng.Value = Mid(Rng, 1, WorksheetFunction.Find("(", Rng) - 2) 'rid of extra space

Works great! Thank you.
the darn .Value!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[gtd526]

In addition to Fluff's needed change, you may also want to change this row:

lastRow = Cells.Find("(", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

to this:

LastRow = Cells(Rows.Count, "BF").End(xlUp).Row

so it runs specifically against column BF.

I incorporated your change in addition to Fluff's.
Thank you.