Multiple Apps

Function that replaces special characters with "regular" Roman characters

Ease of Use

Easy

Version tested with

Excel/Access 2003 

Submitted by:

matthewspatrick

Description:

This UDF will examine a string input, and replace any designated special characters such as accented vowels with "regular" corresponding Roman characters. Thre replacement string can be >1 letter, so German sharp s can be replaced by ss. 

Discussion:

At another site, a user wanted to update a field in an Access table; the field had many special characters such as accented vowels, and the user wanted to return unaccented equivalents. This function allows a user to do just this, and can be customized to fit the particular needs of a given user. For example, to replace 'ö', one user might want to return 'oe', and another might want 'o'; to change between the two, simply change the Item argument in the line in the code where a return value is mapped to 'ö'. Also, while this function was intended for replacing accented characters, you can use it to replace any single character you identify with 1+ other characters; the sample file demonstrates how the function can be made to replace '$' with 'dollar'. NOTE: this function works for ANSI characters only! Using this function with Unicode characters not in the ANSI set can yield unexpected results! 

Code:

instructions for use

			

Option Explicit Function TranslateChar(StrIn As String) As String ' This function may be used in Excel or Access, or in any VB/VBA project. ' Function evaluates an ANSI string that may have special characters, identified ' in the collection populated below. If a special character is found, the function ' replaces that character with a designated replacement string (may be any number ' of characters). There is no support for Unicode. ' The function conserves case, so if the special character is uppercase, then the ' first character of the replacement string will be uppercase as well. ' While the intent of this function is to "replace" characters with diacritical ' marks with their Roman alphabet equivalents (you should feel free to change the ' mapping below if you do not think it's right or it does not suit your purposes; ' I am no linguist). However, you could use the code to replace any single ANSI ' character with whatever string you desire. Dim Counter As Long Static coll As Collection Dim Check As String Dim WasLower As Boolean Dim Letter As String ' See if the collection exists. The collection is set up as a static variable, so ' that it will persist between function calls; that will save a few cycles on later ' function calls as there will be no need to create and populate the collection again. ' There wil be no "Set coll = Nothing" to release the object variable, though; we ' will rely on VBA to clean up the collection object for us when the user exits the ' application If coll Is Nothing Then Set coll = New Collection ' Populate a Collection with the mapping. The Key is the special character, and the ' Item is the replacement. The key must always be a single character, but the item ' may be 1+ characters. Use lower case in this list, and continue the list as ' needed. coll.Add Item:="a", Key:="?" coll.Add Item:="a", Key:="?" coll.Add Item:="a", Key:="?" coll.Add Item:="a", Key:="?" coll.Add Item:="a", Key:="?" coll.Add Item:="a", Key:="?" coll.Add Item:="ae", Key:="?" coll.Add Item:="c", Key:="?" coll.Add Item:="e", Key:="?" coll.Add Item:="e", Key:="?" coll.Add Item:="e", Key:="?" coll.Add Item:="e", Key:="?" coll.Add Item:="i", Key:="?" coll.Add Item:="i", Key:="?" coll.Add Item:="i", Key:="?" coll.Add Item:="i", Key:="?" coll.Add Item:="n", Key:="?" coll.Add Item:="o", Key:="?" coll.Add Item:="o", Key:="?" coll.Add Item:="o", Key:="?" coll.Add Item:="o", Key:="?" coll.Add Item:="o", Key:="?" coll.Add Item:="oe", Key:="?" coll.Add Item:="ss", Key:="?" ' German sharp s coll.Add Item:="th", Key:="?" ' Old English eth coll.Add Item:="th", Key:="?" ' Old English thorn coll.Add Item:="u", Key:="?" coll.Add Item:="u", Key:="?" coll.Add Item:="u", Key:="?" coll.Add Item:="u", Key:="?" coll.Add Item:="y", Key:="?" coll.Add Item:="y", Key:="?" ' This entry is for illustration only! You should remove before using! coll.Add Item:="dollar", Key:="$" End If ' Loop through string to look for special characters needing replacement For Counter = 1 To Len(StrIn) ' Look in collection to see if the current character being considered is a "special" ' character On Error Resume Next Letter = Mid(StrIn, Counter, 1) Check = coll(Letter) ' Check to see if original character was upper or lower case WasLower = (StrComp(Letter, LCase(Letter), vbBinaryCompare) = 0) ' If there was no error, that means character was in collection and thus is a ' special character needing replacement If Err <> 0 Then Err.Clear Check = Letter End If On Error GoTo 0 ' If character was lower case, return the translation in lower case. If upper case, ' return in proper case (first character capitalized) TranslateChar = TranslateChar & IIf(WasLower, LCase(Check), StrConv(Check, vbProperCase)) Next End Function

How to use:

  1. From the Access or Excel UI, hit Alt+F11 to enter the VB Editor
  2. Right-click your project in the Project Explorer, and insert a new module into the project
  3. Copy and paste the code into that module
  4. Edit the section of "coll.Add ..." lines as needed to map the special characters to your desired return values
  5. Close the VB Editor
  6. Use the function elsewhere in the VB Project, or in worksheet formulas (Excel) or in queries, report, and forms (Access)
 

Test the code:

  1. Download the sample file
  2. Enter strings with special characters (from the ANSI set!), and see whether the function returns the desired output
 

Sample File:

Example.zip 12KB 

Approved by lucas


This entry has been viewed 94 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express