Delete Specific Cell Content Based on Criteria

[Barlew]

Hello

I have about 1000 Spreadsheets that I’m working with, that all need their template updated. Using a combination of Macro Express and Excel VBA I’m nearly at a point where the update will be completely automated. The final piece of my jigsaw is as follows:

I need to delete some text from a specific cell. This cell might be in a slightly different reference in each of the 1000 sheets, BUT will always be adjacent to another cell containing unique text. SO what I need is VBA to:

• Find the cell containing the text "Test description"
• Look into the cell to the right of it
• Find the characters " - " (that’s space dash space) in that cell
• Then delete " - " and everything before it

There maybe more instances of " - " I’m only interested in the first " - " found.

Thanks

 

[Rick Rothstein]

I missed the requirement for the "TS" needing to be in cell with the " - ". Here is my code (still a one-liner), modified to account for this (and presented as a normal macro)...

Sub FixCellContent()
  Cells.Find("Test description", , xlValues, xlWhole, , , False).Offset(, 1).Replace "*TS* - ", "", xlPart, , True
End Sub