Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Can you share the macro (assuming it's VBa code) –  Dave Rook Sep 14 '12 at 14:52
    
What version of Excel are you running? –  dangowans Sep 16 '12 at 15:02
add comment

4 Answers

up vote 1 down vote accepted

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
share|improve this answer
    
You are great!! Thank you so much for such a great help, its a great macro for me to work with. It helped me alot. Thanks once again. –  Asim Sep 24 '12 at 14:47
add comment

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.

Find and Replace

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.

share|improve this answer
    
Thanks again problem resolved by chris neilsen Macro –  Asim Sep 24 '12 at 14:50
    
Thanks! I didn’t know about that one. –  Scott Sep 24 '12 at 16:40
    
… but you might want to edit your answer to say “Highlight the record s you want to update” (“I want to change … in whole Excel workbook”) and maybe say something about Shift+click and Ctrl+click. –  Scott Sep 24 '12 at 16:41
add comment

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

enter image description here

share|improve this answer
1  
Sorry, but I think it is (fairly) obvious that the cells with text to be replaced include more than just INT or NA. –  pnuts Sep 14 '12 at 15:40
1  
@pnuts: FWIW, I believe that's not obvious from the question. –  Scott Sep 14 '12 at 17:10
    
@Scott I'd agree, not blatant, but what else could "likewise character INT which is not attach to any other character" mean? –  pnuts Sep 17 '12 at 12:33
    
@pnuts: Given that the questioner seems to be struggling with English, "likewise character INT which is not attach to any other character" could very well mean the same as "the character NA in the workbook which is not included with any other character"; i.e., "string X in a cell by itself, without any other characters." –  Scott Sep 17 '12 at 15:22
    
Scott is right, sorry for my weak English, likewise mean as the case mention before. But its solved now by chris neilsen. –  Asim Sep 24 '12 at 14:46
show 1 more comment
  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.
share|improve this answer
    
Thank you so much for your help but its a lengthy process. –  Asim Sep 24 '12 at 14:47
1  
And you believe that a 700+ character VBA routine is faster? –  Scott Sep 24 '12 at 16:12
1  
@Scott It is if the code is provided for you. :) –  Excellll Sep 25 '12 at 21:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.