Hi i need a word VBA script that will clean any non alpha-numeric characters from a…
- User
Hi i need a word VBA script that will clean any non alpha-numeric characters from a string, like the regex [a-zA-Z1-0]. Can you help?
Assistant
VBA Script to Clean Non-Alpha-Numeric Characters
Here's a VBA function that uses a regular expression to remove non-alpha-numeric characters from a string.
Function CleanNonAlphaNumeric(inputString As String) As String Dim regex As Object Set regex = CreateObject("VBScript.RegExp") With regex .Global = True .Pattern = "[^a-zA-Z0-9]" CleanNonAlphaNumeric = .Replace(inputString, "") End With Set regex = Nothing End Function Sub TestCleanNonAlphaNumeric() Dim testString As String testString = "Hello, World! 123" Debug.Print "Original String: " & testString Debug.Print "Cleaned String: " & CleanNonAlphaNumeric(testString) End SubHow it Works
- The
CleanNonAlphaNumericfunction takes an input string and uses a regular expression to replace non-alpha-numeric characters with an empty string. - The regular expression
[^a-zA-Z0-9]matches any character that is not a letter (both uppercase and lowercase) or a digit. - The
TestCleanNonAlphaNumericsubroutine demonstrates how to use theCleanNonAlphaNumericfunction.
Using the Function
- Open the Visual Basic Editor in your Word document by pressing
Alt + F11or navigating toDeveloper > Visual Basic. - In the Visual Basic Editor, insert a new module by clicking
Insert > Module. - Paste the code into the new module.
- You can now use the
CleanNonAlphaNumericfunction in your VBA code or as a user-defined function in your Word document.
Example Usage
To use the function in a Word document, you can create a new macro that calls the function and assigns the result to a variable or a document variable.
Sub ExampleUsage() Dim cleanedString As String cleanedString = CleanNonAlphaNumeric("Hello, World! 123") MsgBox cleanedString End Sub- The
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.