Find strings within cells and replace with string from a table

**MMSte** · 05-09-2012, 04:44 AM

Good morning all, hopefully you can help me.

I have a column of data which I need to tidy up. The data is made up of various strings, and I wish to replace various portions of those strings, with a predefined sanitised string contained within a table.

In the example below, I wish to search to recursively through each string, and see if a match can be identified against the bold text, with a clean string substituted in.

BEFORE

63,Euroconsult Mott Mc.Donald,144.100,MM Croydon, BoardRm,
64,MM Arnhem,144.100,Mott MacDonald, Croydon, Mezz,
64,MM Arnhem,144.100,MM Croydon, BoardRm,

AFTER

63,MM Arnhem,144.100,MM Croydon,
64,MM Arnhem,144.100,MM Croydon,
64,MM Arnhem,144.100,MM Croydon,

The sanitised data is contained in a separate table in the following format:

COLUMN A

MM Croydon, BoardRm
Mott MacDonald, Croydon, Mezz,
Euroconsult Mott Mc.Donald

COLUMN B

MM Croydon
MM Croydon
MM Arnhem

I have attempted to use the SUBSTITUTE function, contained within nested IF functions, but above two or three different levels, the formulae becomes to unwieldy, as my sanitised table is about 40 rows long.

Any help most appreciated.

Regards,

S

**Bob Phillips** · 05-09-2012, 05:08 AM

Nice and simple

Sub test()
Dim strVariable As String
Dim nTime As Double
Dim i As Long

    nTime = Timer
    For i = 1 To 100000000
    
        If Len(strVariable) > 0 Then
             'Using 100,000,000 Loops - Time: 1.418 seconds
        End If
    Next i
    Debug.Print Timer - nTime
    
    
    nTime = Timer
    For i = 1 To 100000000
    
         If strVariable <> "" Then
             'Using 100,000,000 Loops - Time: 3.18 seconds
        End If
    Next i
    Debug.Print Timer - nTime
     
     
    nTime = Timer
    For i = 1 To 100000000
    
         If strVariable <> vbNullString Then
             'Using 100,000,000 Loops - Time: 3.105 seconds
        End If
    Next i
    Debug.Print Timer - nTime
End Sub

Sub ReplaceText()
Dim vecBefore As Variant
Dim vecAfter As Variant
Dim lastrow As Long
Dim pos As Long
Dim i As Long, j As Long

    Application.ScreenUpdating = False
    
    With Worksheets("Sheet2")
    
        vecBefore = Application.Transpose(.Range(.Range("A1"), .Range("A1").End(xlDown)))
        vecAfter = Application.Transpose(.Range(.Range("B1"), .Range("B1").End(xlDown)))
    End With
    
    With Worksheets("Sheet1")
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To lastrow
        
            For j = LBound(vecBefore) To UBound(vecBefore)
            
                pos = InStr(.Cells(i, "A").Value, vecBefore(j))
                If pos > 1 Then
                
                    .Cells(i, "A").Value = Replace(.Cells(i, "A").Value, vecBefore(j), vecAfter(j))
                End If
            Next j
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

**MMSte** · 05-14-2012, 08:03 AM

Thanks for the response, as I'm not very experienced with VBA, I carried out the following steps:

Opened up the VBA editor
Created a new module
Pasted the code in
Replaced the reference to "Sheet2" to "NameChanges" - i.e the sheet containing the orginal names in column A, and the correct version in column B
Replaced the reference to "Sheet1" to "Raw Data" - i.e the sheet containing the records where by the strings to be replaced are contained within column A, on rows 1, 2, 3..., X
Pressed F5, and unfortunatley nothing happen.

I also tried this with Sheet2 and Sheet1 named the other way around.

I'm obviously missing something!

Many thanks,

Stepehn

**Bob Phillips** · 05-14-2012, 09:12 AM

I think I had some old code in my module that I inadvertently copied in. You should be able to get rid of test procedure and just run the ReplaceText procedure.

**MMSte** · 05-14-2012, 09:47 AM

Originally Posted by Bob Phillips

I think I had some old code in my module that I inadvertently copied in. You should be able to get rid of test procedure and just run the ReplaceText procedure.

Thanks for the continued help.

I tried again, just using the ReplaceText procedure, but no luck. If you are still willing to help, attached is a workbook containing my name change sheet, and a very short extract of the data I am attempting to clean up.

Thank you

Stephen

P.S One final thought, would your method work if the string were mentioned more than once in the same string?

**Bob Phillips** · 05-14-2012, 10:00 AM

Stephen,

It needed a few small changes to reflect the data you actually have.

Before running the macro, select the sheet to be tidied up.

Sub ReplaceText()
Dim vecBefore As Variant
Dim vecAfter As Variant
Dim lastrow As Long
Dim pos As Long
Dim i As Long, j As Long

    Application.ScreenUpdating = False
    
    With Worksheets("Name Changes")
    
        vecBefore = Application.Transpose(.Range(.Range("A3"), .Range("A3").End(xlDown)))
        vecAfter = Application.Transpose(.Range(.Range("B3"), .Range("B3").End(xlDown)))
    End With
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To lastrow
        
            For j = LBound(vecBefore) To UBound(vecBefore)
            
                pos = InStr(.Cells(i, "A").Value, vecBefore(j))
                If pos > 1 Then
                
                    .Cells(i, "A").Value = Replace(.Cells(i, "A").Value, vecBefore(j), vecAfter(j))
                End If
            Next j
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

**MMSte** · 05-14-2012, 12:22 PM

Brilliant!

Works like a charm.

Thanks for all your help

Find strings within cells and replace with string from a table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Re: Find strings within cells and replace with string from a table

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions