Excel Smart Find and Replace only specific characters

Question

I want to change INT to INTERNATIONAL and NA to NATIONAL ASSEMBLY in whole excel workbook through an excel Macro or Find and Replace dialogue box. But when I run the macro or change it through Find and Replace dialogue box it also replace NA from CHINA last 2 characters and it became CHINATIONAL ASSEMBLY and INTERIOR to INTERNATIONALERIOR.

Now, I want that Excel should only smartly find the character NA in the workbook which is not included with any other character likewise character INT which is not attach to any other character.

Answer 1

If you go the VBA route, try using Regular Expressions

Here's a start

Sub ReplaceWithRE()
    Dim re As Object 'RegExp
    Dim rng As Range, cl As Range
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim sReplace As String
    Dim aReplace(0 To 1, 0 To 1) As String
    Dim i As Long

    Set wb = ActiveWorkbook
    Set re = CreateObject("vbscript.regexp") ' New RegExp
    re.Global = True
    re.IgnoreCase = False
    re.MultiLine = True

    ' Load array of patterns and replacements
    aReplace(0, 0) = "\bINT\b"
    aReplace(0, 1) = "INTERNATIONAL"
    aReplace(1, 0) = "\bNA\b"
    aReplace(1, 1) = "NATIONAL ASSEMBLY"

    For Each sh In wb.Worksheets
        On Error Resume Next
        Set rng = sh.UsedRange.SpecialCells(xlCellTypeConstants)
        If Err.Number <> 0 Then
            Err.Clear
        Else
            On Error GoTo 0
            For Each cl In rng
                sReplace = cl.Value
                ' Test each cell for each pattern, replace when found
                For i = 0 To UBound(aReplace, 1)
                    re.Pattern = aReplace(i, 0)
                    If re.Test(sReplace) Then
                        sReplace = re.Replace(sReplace, aReplace(i, 1))
                    End If
                Next
                cl.Value = sReplace
            Next
        End If
    Next


End Sub

Answer 2

If the values you are looking to replace are by themselves in the cells (no other text), definitely follow CharlieRB's solution. If the values are located in the cells, along with additional text, a slightly modified version would work.

Open the Find and Replace window. Click the "Replace" tab.

Enter the "Find what" and the "Replace with" values. Click the "Find All" button.

Review the list at the bottom that details what records were found. Highlight the record you want to update, and click the "Replace" button.

Answer 3

In the Find and Replace window, you need to check the box Match entire cell contents then run it.

Answer 4

1. Save your file as a CSV file.  If you want to manipulate a subset of your worksheet (e.g., selected columns and/or rows), copy that region into a scratch file (or sheet) and save that as CSV.  If you need to modify multiple sheets, repeat this process for each sheet.
2. Edit the CSV file with some sort of smart text editor (i.e., smarter than Notepad).  vi is good for this; use a command such as %s/\<INT\>/INTERNATIONAL/g.  Microsoft Word will probably suffice; use the “Find whole words only” option under More>> in “Find and Replace”.
3. Read the edited CSV file back into Excel.
4. Copy the modified values into your original workbook, to preserve formatting.