VBA Remove variable text from a string

[decadence]

Hi , Is there a way to remove variable text from a string in a cell, there will be variable rows of data that may or may not contain the W/MW values, and it's not case sensitive either. Can someone help with this please

From this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Voltage Switch 1/16W Ceramic Wirewound Regulator[/TD]
[/TR]
[TR]
[TD]Ceramic Wirewound 1/10MW[/TD]
[/TR]
[TR]
[TD]5/100W Differential tactile fuse[/TD]
[/TR]
</tbody>[/TABLE]

To this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Voltage Switch Ceramic Wirewound Regulator[/TD]
[/TR]
[TR]
[TD]Ceramic Wirewound[/TD]
[/TR]
[TR]
[TD]Differential tactile fuse[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, here is one way you could try - assumes your values are in column A - test on a copy of your data.

Sub M()
Dim a As Variant, b As Variant, i As Long
a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
For i = 1 To UBound(a)
    For Each b In Split(a(i, 1), " ")
        If UCase(b) Like "*/*W" Then
            a(i, 1) = Replace(a(i, 1), b, "")
        End If
    Next b
    a(i, 1) = Application.Trim(a(i, 1))
Next i
Range("A1").Resize(UBound(a)).Value = a
End Sub

 

[decadence]

This works perfectly, Thank you