Need to replace text in 1 column

[Crocdundee]

Hi, I have a column with many rows with duplicate text, which I wish to replace with other text .
Example:
Hobins
G'lonS
Gunndh
Mphvl
Suncst
Kalgl.
This is only a small example and starting from the top I need to replace with this text and do so on a daily basis.
Hobins replace with Hobart
G'lonS Replace with Geelong
and so forth
So I was thinking of a text file to hold the text and its corresponding replacement.
OR maybe an array which I know nothing of.
Any help with this would be most helpful
Regards
Graham

 

[AlphaFrog]

This is a simple macro that will do what you want I think.

The macro assumes your daily data with duplicates is in Sheet1 column A

On Sheet2 column A have the list you want to find and column B have the list you want to replace with. The macro will replace the items from the list in column A with the corresponding items from the list in column B.

To install the macro...

• Alt+F11 to open the VBA editor
• From the VBA menu, select Insert\ Module
• Paste the code below in the VBA edit window

Sub Replacer()
    
    Dim cell As Range
    
    With Sheets("Sheet2")
        For Each cell In .Range("A1", .Range("A" & Rows.Count))
            If cell <> "" Then
                Sheets("Sheet1").Range("A:A").Replace _
                                    What:=cell.Value, _
                                    Replacement:=cell.Offset(, 1).Value, _
                                    LookAt:=xlWhole, _
                                    MatchCase:=False
            End If
        Next cell
    End With
    
    MsgBox "Replacements Complete", vbInformation
    
End Sub

 

[Crocdundee]

Thank you very much indeed
I always Appreciate the efforts you guys put in
Regards
Graham