VBA function declaration to remove unwanted characters from a name Ms Access Gurus

Help support this site, thank you.

Home Articles Code Tools

VBA function to Correct a Name by replacing bad or unwanted characters with underscore

Replace bad and unwanted characters in strings with underscore. Easy-to-use VBA function with a list of characters to be replaced with underscore that you can modify if desired.

Call from VBA or use in an expression for a query or control source.

replace bad and unwanted characers with underscore

Quick Jump

Download VBA Reference Backstory Share Donate

Goto Top  

Download

Download zipped BAS file you can import into your VBA projects for Access, Excel, and other apps when you need it. mod_Fx_CorrectName_s4p.zip

If you have trouble with a download, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

VBA

Standard module 

' module name: mod_CorrectName_s4p
'*************** Code Start ***************************************************
' Purpose  : replace unwanted characters in string with underscore (_)
' Author   : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/Fx_CorrectName.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
'--------------------------------------------------------------------------------
'                              CorrectName_s4p
'--------------------------------------------------------------------------------'
Function CorrectName_s4p( _ 
   ByVal psName As String _ 
   ) As String 
'strive4peace 221223, 230129
' replace spaces and unwanted characters with underscore _
' if 2 in a row, only use 1
' trim beginning and end

   Dim i As Integer _ 
      ,sName As String _ 
      ,sChar As String * 1 _ 
      ,sLastChar As String * 1 _ 
      ,sNewChar As String * 1 _ 
      ,iPos As Integer 
 
   'PARAMETERS
   'psName is the string you want to correct
 
   'EXAMPLE USEAGE
   '  on the AfterUpdate event of a control
   '  =CorrectName([controlname])
   '
   'in a query:
   'field --> CorrectName: CorrectName_s4p([strFieldname])
 
   'EXAMPLE
   ' ? CorrectName_s4p("as(,48209j@##@!")
   ' --> as_48209j_
 
   CorrectName_s4p =  ""
   If psName =  "" Then Exit Function 
   
   Dim sBadCharacters As String 
   sBadCharacters =  "`!@#$%^&*()+-=|\:;""'<>,.?/ "
 
   psName = Trim(psName) 
 
   For i = 1 To Len(psName) 
      sChar = Mid(psName,i,1) 
 
      If InStr(sBadCharacters,sChar) > 0 Then 
         sNewChar =  "_"
      Else 
         sNewChar = sChar 
      End If 
 
      If sLastChar =  "_" And sNewChar =  "_" Then 
         'leave the same for multiple characters to replace in a row
      Else 
         sName = sName & sNewChar 
      End If 
 
      sLastChar = sNewChar 
   Next i 
 
   CorrectName_s4p = sName 
 
End Function 
'*************** Code End *****************************************************
' Code was generated with colors using the free Color Code add-in for Access.

Goto Top  

Reference

Microsoft

Len function

LTrim, RTrim, and Trim functions

Mid function

And operator

For...Next statement

Goto Top  

Backstory

Oftentimes, strings you want to use as names for field or objects have characters that you don't want!

This function helps you correct them quickly

If you like this page, please let me know. Donations, big and small, are much appreciated! They all say Thank you.

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/Fx_CorrectName.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/Fx_CorrectName.htm

Get Help with Access and converting data

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Access is great at converting and managing data from all kinds of different places. Access is access! I'd love to help you. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top