none
Working with Replace Function and multiple values RRS feed

  • Question

  •  

    Hello everyone,

    I wrote this simple VBA code to replace certain words in an Address Column using the Replace Function.
    Example: I replaced the word Avenue with Ave; the code is placed in a Module in Access.

    My question do I need to create multiple replace functions or is there a way to expand this code?

    Public Function TrmChar(ReplaceChar As String)
    ReplaceChar = Replace(ReplaceChar, "Avenue", "Ave")
    TrmChar = ReplaceChar
    End Function

    Than in my query:

    SELECT TrmChar([ADDRESS]) FROM tblPersons;


    I'm trying to clean up the address, do I need to create multiple Replace function?

    Thanks - everyone!

     

    Sunday, December 18, 2011 7:33 AM

Answers

  • On Sun, 18 Dec 2011 11:51:55 +0000, Gary's Student [MVP] wrote:
     
    >
    >
    >First let me say that I know nothing about Access.  If the code posted below (Excel VBA) can not be adapted to Access, then ignore this response:
    >
    >
    >
    >Public Function TrmChar(ReplaceChar As String) As String
    >Originals = Array("Avenue", "Road", "Lane")
    >Replacements = Array("Ave.", "Rd.", "Ln.")
    >TrmChar = ReplaceChar
    >For i = 0 To 2
    >    TrmChar = Replace(TrmChar, Originals(i), Replacements(i))
    >Next
    >End Function
    >gsnu201111
     
     
    That's good.  I would suggest one small change to ensure it being case insensitive:
     
    =================
    Option Explicit
    Public Function TrmChar(ReplaceChar As String) As String
    Dim Originals As Variant, Replacements As Variant
    Dim i as Long
    Originals = Array("Avenue", "Road", "Lane")
    Replacements = Array("Ave.", "Rd.", "Ln.")
    TrmChar = ReplaceChar
    For i = 0 To 2
        TrmChar = Replace(TrmChar, Originals(i), Replacements(i), compare:=vbTextCompare)
    Next
    End Function
    ==========================
     

    Ron


    Thanks for your valuable input!

     

    Synth

    • Marked as answer by Synthologic Sunday, December 18, 2011 9:19 PM
    Sunday, December 18, 2011 9:19 PM

All replies

  • On Sun, 18 Dec 2011 07:33:26 +0000, Synthologic wrote:
     
    >
    >
    >
    >Hello everyone,
    >
    >I wrote this simple VBA code to replace certain words in an Address Column using the Replace Function.
    >Example: I replaced the word Avenue with Ave; the code is placed in a Module in Access.
    >
    >My question do I need to create multiple replace functions or is there a way to expand this code?
    >
    >Public Function TrmChar(ReplaceChar As String)
    >ReplaceChar = Replace(ReplaceChar, "Avenue", "Ave")
    >TrmChar = ReplaceChar
    >End Function
    >Than in my query:
    >
    >SELECT TrmChar([ADDRESS]) FROM tblPersons;
    >
    >
    >I'm trying to clean up the address, do I need to create multiple Replace function?
    >
    >Thanks - everyone!
    >
     
    I would do something like this:
     
    =========================
    Option Explicit
    Sub CleanUpAddresses()
        Dim rg As Range, c As Range
        Dim vLongName As Variant
        Dim vAbbrev As Variant
        Dim i As Long
    vLongName = Array("Avenue", "Street", "Boulevard", "Court", "Highway")
    vAbbrev = Array("Ave", "St", "Blvd", "Ct", "Hwy")
     
    Set rg = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    For Each c In rg
        For i = LBound(vLongName) To UBound(vLongName)
            If InStr(1, c.Text, vLongName(i), vbTextCompare) > 0 Then
                c(1, 2).Value = Replace(c.Text, vLongName(i), vAbbrev(i), compare:=vbTextCompare)
            End If
        Next i
    Next c
    End Sub
    =============================
     
    Or, as a function:
     
    ===============================
    Option Explicit
    Function CleanUpAddress(s As String)
        Dim vLongName As Variant
        Dim vAbbrev As Variant
        Dim i As Long
    vLongName = Array("Avenue", "Street", "Boulevard", "Court", "Highway")
    vAbbrev = Array("Ave", "St", "Blvd", "Ct", "Hwy")
     
    For i = LBound(vLongName) To UBound(vLongName)
        If InStr(1, s, vLongName(i), vbTextCompare) > 0 Then
            CleanUpAddress = Replace(s, vLongName(i), vAbbrev(i), compare:=vbTextCompare)
        End If
    Next i
    End Function
    ================================
     

    Ron
    Sunday, December 18, 2011 11:39 AM
  • First let me say that I know nothing about Access.  If the code posted below (Excel VBA) can not be adapted to Access, then ignore this response:

     

     

    Public Function TrmChar(ReplaceChar As String) As String
    Originals = Array("Avenue", "Road", "Lane")
    Replacements = Array("Ave.", "Rd.", "Ln.")
    TrmChar = ReplaceChar
    For i = 0 To 2
        TrmChar = Replace(TrmChar, Originals(i), Replacements(i))
    Next
    End Function


    gsnu201111
    Sunday, December 18, 2011 11:51 AM
    Moderator
  • On Sun, 18 Dec 2011 11:51:55 +0000, Gary's Student [MVP] wrote:
     
    >
    >
    >First let me say that I know nothing about Access.  If the code posted below (Excel VBA) can not be adapted to Access, then ignore this response:
    >
    >
    >
    >Public Function TrmChar(ReplaceChar As String) As String
    >Originals = Array("Avenue", "Road", "Lane")
    >Replacements = Array("Ave.", "Rd.", "Ln.")
    >TrmChar = ReplaceChar
    >For i = 0 To 2
    >    TrmChar = Replace(TrmChar, Originals(i), Replacements(i))
    >Next
    >End Function
    >gsnu201111
     
     
    That's good.  I would suggest one small change to ensure it being case insensitive:
     
    =================
    Option Explicit
    Public Function TrmChar(ReplaceChar As String) As String
    Dim Originals As Variant, Replacements As Variant
    Dim i as Long
    Originals = Array("Avenue", "Road", "Lane")
    Replacements = Array("Ave.", "Rd.", "Ln.")
    TrmChar = ReplaceChar
    For i = 0 To 2
        TrmChar = Replace(TrmChar, Originals(i), Replacements(i), compare:=vbTextCompare)
    Next
    End Function
    ==========================
     

    Ron
    Sunday, December 18, 2011 1:23 PM
  • On Sun, 18 Dec 2011 11:51:55 +0000, Gary's Student [MVP] wrote:
     
    >
    >
    >First let me say that I know nothing about Access.  If the code posted below (Excel VBA) can not be adapted to Access, then ignore this response:
    >
    >
    >
    >Public Function TrmChar(ReplaceChar As String) As String
    >Originals = Array("Avenue", "Road", "Lane")
    >Replacements = Array("Ave.", "Rd.", "Ln.")
    >TrmChar = ReplaceChar
    >For i = 0 To 2
    >    TrmChar = Replace(TrmChar, Originals(i), Replacements(i))
    >Next
    >End Function
    >gsnu201111
     
     
    That's good.  I would suggest one small change to ensure it being case insensitive:
     
    =================
    Option Explicit
    Public Function TrmChar(ReplaceChar As String) As String
    Dim Originals As Variant, Replacements As Variant
    Dim i as Long
    Originals = Array("Avenue", "Road", "Lane")
    Replacements = Array("Ave.", "Rd.", "Ln.")
    TrmChar = ReplaceChar
    For i = 0 To 2
        TrmChar = Replace(TrmChar, Originals(i), Replacements(i), compare:=vbTextCompare)
    Next
    End Function
    ==========================
     

    Ron


    Thanks for your valuable input!

     

    Synth

    • Marked as answer by Synthologic Sunday, December 18, 2011 9:19 PM
    Sunday, December 18, 2011 9:19 PM