Replace a specific string in excel

[Gabriell]

Dear Members,

I need a solution to replace a specific string in an Excel file, but only when it meets certain criteria.

For example, in the database, I have different strings, in different columns, like "BRAK" and "BRAKE." The goal is to replace "BRAK" with "-", while leaving "BRAKE" unchanged.

What would be the best way to achieve this?

Thank you in advance!
Gabor

 

[HighAndWilder]

Dear Members,

I need a solution to replace a specific string in an Excel file, but only when it meets certain criteria.

For example, in the database, I have different strings, in different columns, like "BRAK" and "BRAKE." The goal is to replace "BRAK" with "-", while leaving "BRAKE" unchanged.

What would be the best way to achieve this?

Thank you in advance!
Gabor

Taken from this post: VBA Bulk Find and Replace or Similar

Substitute the values in the arrays FindThese and ReplaceWith as appropriate for your needs.

Test on a copy of your data as Ctrl-Z will not work.

Only Whole cell values are replaced.

' Check for all cells in a workbook.
Public Sub ReplacementsE1()
Dim X As Long, FindThese As Variant, ReplaceWith As Variant
  
  FindThese = Array("Royal Mail", "CRL24", "TPN24")
  
  ReplaceWith = Array("TPN24", "Royal Mail", "TRACKED")
  
  For X = LBound(FindThese) To UBound(FindThese)
    Worksheets("Sheet2").Cells.Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
  Next

End Sub

'  Check for cells in specified columns only.
Public Sub ReplacementsE2()
Dim X As Long, FindThese As Variant, ReplaceWith As Variant
  
  FindThese = Array("Royal Mail", "CRL24", "TPN24")
  
  ReplaceWith = Array("TPN24", "Royal Mail", "TRACKED")
  
  For X = LBound(FindThese) To UBound(FindThese)
    Worksheets("Sheet2").Columns("B:C").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
  Next

End Sub

 

[Automatrix]

1. Replace BRAKE with something else, like §§§§§.
2. Now replace BRAK with -.
3. Then change §§§§§ back to BRAKE.

 

[Gabriell]

1. Replace BRAKE with something else, like §§§§§.
2. Now replace BRAK with -.
3. Then change §§§§§ back to BRAKE.

Thanks, this worked.

 

[Gabriell]

Taken from this post: VBA Bulk Find and Replace or Similar

Substitute the values in the arrays FindThese and ReplaceWith as appropriate for your needs.

Test on a copy of your data as Ctrl-Z will not work.

Only Whole cell values are replaced.

' Check for all cells in a workbook.
Public Sub ReplacementsE1()
Dim X As Long, FindThese As Variant, ReplaceWith As Variant
 
  FindThese = Array("Royal Mail", "CRL24", "TPN24")
 
  ReplaceWith = Array("TPN24", "Royal Mail", "TRACKED")
 
  For X = LBound(FindThese) To UBound(FindThese)
    Worksheets("Sheet2").Cells.Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
  Next

End Sub

'  Check for cells in specified columns only.
Public Sub ReplacementsE2()
Dim X As Long, FindThese As Variant, ReplaceWith As Variant
 
  FindThese = Array("Royal Mail", "CRL24", "TPN24")
 
  ReplaceWith = Array("TPN24", "Royal Mail", "TRACKED")
 
  For X = LBound(FindThese) To UBound(FindThese)
    Worksheets("Sheet2").Columns("B:C").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
  Next

End Sub

I couldn't configure it in the right way. Thanks anyway!