As a Visual Basic for Applications programmer, you should be able to remove numbers or characters in strings to display correct information to the user. In VBA, you can use the "Trim" function to remove any spaces in the beginning and end of a string. The "Left" and "Right" functions are used to return a specified number of characters from the left or right side of a string. The "Replace" function returns a string in which a specified substring has been replaced with another.
Start by typing the following to create a new VBA sub procedure:
Private Sub cleanString()
Create and define a variable with a string:
Dim StrVar As String
StrVar = " 2Remove numbers and characters 432*% from this string.33 "
Type the following to remove the space characters in the beginning and end of the string:
StrVar = Trim(StrVar)
Type the following to remove "33" from the end of the string:
StrVar = Left(StrVar, Len(StrVar) - 2)
Type the following to remove "2" from the beginning of the string:
StrVar = Right(StrVar, Len(StrVar) - 1)
Type the following to remove "432*%" from the string:
StrVar = Replace(StrVar, "432*%", "")
Type the following to replace a double-space character with a single space in the string:
StrVar = Replace(StrVar, " ", " ")
Display a message box with the final string by typing the following:
Type "End Sub" to end your procedure and press "F5" to run the VBA code.
- Photo Credit John Foxx/Stockbyte/Getty Images
Access 2007: The VBA Set String Variable to Null
Visual Basic for Applications (VBA) is a programming language used for Microsoft Access. You set a string variable to null to clear...
How to Find a Character in a String in VBA
Finding a single character in a string using VBA is a special instance of finding one string within another. The process itself...
How to Convert a String to a Date in VBA
Visual Basic for Applications, or VBA, is Microsoft's scripting language that allows for the automation of Office products such as Excel, Word...
How to Remove Characters in a Cell on Excel 2007 With VBA
Microsoft's Visual Basic for Applications (VBA) is a subset of Visual Basic that is used to customize Microsoft Office applications. A piece...
How to Check for a Tab Character in VBA
Sooner or later you will run into a String variable where you need to check each character and identify a tab when...
How to Remove Whitespace From a String in Excel
Microsoft Excel comes with an arsenal of tools that can help you efficiently perform data clean-up tasks. For instance, to remove whitespace...
How to Convert a String to Int in Excel
When you import numbers from another source, spaces and non-printing characters sometimes accompany the numbers. These non-numeric characters can make Excel 2013...
How to Concatenate in VBA
When you concatenate multiple items, you essentially combine them to form a single string or number. So, for example, if you were...