Remove a word from the cells of a column

Hello,

With an ActiveX button
and two parameters TRANSPOTS and TRANSPORT on a variable range column A

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 2 To Range("A65536").End(xlUp).Row
Cells(i, 1).Replace What:="TRANSPORTS", Replacement:=""
Cells(i, 1).Replace What:="TRANSPORT", Replacement:=""
Next
End Sub

Best regards,
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Hello,

Thank you for the reply.

If possible, a formula would suit me, as several people will likely be updating the database.

Some VBA would be even better, I think.

Best regards.

Hello,

Select the columns to modify, then press Ctrl+F => Replace => search: Transport and leave it blank to replace => replace all

Do the same for Transports.

Best regards.

Hello

for VBA, I don't know...except for a macro with a button or a keyboard command that will execute the replacement on the same principle as proposed
so this, which will remove transport in the selected field

Sub CHANGE
Selection.Replace What:="Transport", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

for the formula:

=SUBSTITUTE(A1,"Transport","")

sincerely

To err is human, to persist is diabolical

Thank you very much :)