Find and replace question

[Routus]

Hi all

Can someone advise me how to solve this challenge please?

I have an Excel workbook with around 25k rows, and I want to replace the contents of cells in column K with something I can quickly search.

Here is an example of one of the cells contents:

"Go Racing With Vickers.Bet National Hunt Flat Race (Conditional/Amateurs') (Cat 1 Elimination) (GBB)"

In this example I want to delete everything to the left and right of 'National Hunt Flat', and replace 'National Hunt Flat' with 'NHF'


There are probably around 20 combinations of words that I want to treat this way, but if someone would be kind enough to explain how I can do this one (I'm not an Excel expert) with a formula, or create a process to do it, I can then modify it to accommodate all of the other variants.

Many thanks

Paul

 

[Routus]

Hi all

Can someone advise me how to solve this challenge please?

I have an Excel workbook with around 25k rows, and I want to replace the contents of cells in column K with something I can quickly search.

Here is an example of one of the cells contents:

"Go Racing With Vickers.Bet National Hunt Flat Race (Conditional/Amateurs') (Cat 1 Elimination) (GBB)"

In this example I want to delete everything to the left and right of 'National Hunt Flat', and replace 'National Hunt Flat' with 'NHF'


There are probably around 20 combinations of words that I want to treat this way, but if someone would be kind enough to explain how I can do this one (I'm not an Excel expert) with a formula, or create a process to do it, I can then modify it to accommodate all of the other variants.

Many thanks

Paul

I have figured this out now.

 

[SunnyAlv]

use this VBA Code,
if u need help, le me know
thanks

Sub Replace()

'Rename with ur Sheet name
Dim S1 As Worksheet
Set S1 = ThisWorkbook.Sheets("Sheet1")



Dim i As Integer

'Change with ur 20 Combination word Column
For i = 1 To Application.CountA(S1.Range("O:O"))


'Action to replace 20 combination word of "National Hunt Flat" with "NHF"
S1.Range("K:K").Replace What:=S1.Range("O:O").Value, Replacement:="NHF"


Next i

MsgBox "Done"

End Sub