Menu
I am trying to create a piece of code that replaces one word with another. Example: Replace Avenue with Ave and North with N. I am using MS Access, I could use SQL REPLACE Function but I want to do this in VBA using Access module so that I can attached the function to other column.
I am not sure where to start with this, so any input will be greatly appreciated.
Guy
254
Use the VBA Replace function to replace a substring of characters in a string with a new string. VBA Replace is similar to the Excel SUBSTITUTE function; both can be used to replace a portion of a string with another.
The VBA REPLACE function is listed under the text category of VBA functions. When you use it in a VBA code, it replaces a substring from string with a new sub-string. In simple words, you can use REPLACE to replace a part of text with another text and it returns that new text in the result.
Keyboard shortcut Press CTRL+F. The Find and Replace dialog box appears. To find data, in the Find and Replace dialog box, click the Find tab. To run a find-and-replace operation, click the Replace tab.
The Microsoft Access Replace function replaces a sequence of characters in a string with another set of characters (a number of times).
Use Access's VBA function Replace(text, find, replacement):
Dim result As String result = Replace("Some sentence containing Avenue in it.", "Avenue", "Ave") I was reading this thread and would like to add information even though it is surely no longer timely for the OP.
BiggerDon above points out the difficulty of rote replacing "North" with "N". A similar problem exists with "Avenue" to "Ave" (e.g. "Avenue of the Americas" becomes "Ave of the Americas": still understandable, but probably not what the OP wants.
The replace() function is entirely context-free, but addresses are not. A complete solution needs to have additional logic to interpret the context correctly, and then apply replace() as needed.
Databases commonly contain addresses, and so I wanted to point out that the generalized version of the OP's problem as applied to addresses within the United States has been addressed (humor!) by the Coding Accuracy Support System (CASS). CASS is a database tool that accepts a U.S. address and completes or corrects it to meet a standard set by the U.S. Postal Service. The Wikipedia entry https://en.wikipedia.org/wiki/Postal_address_verification has the basics, and more information is available at the Post Office: https://ribbs.usps.gov/index.cfm?page=address_info_systems
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
