How to Clean a String in VBA


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:

    MsgBox StrVar

  • Type "End Sub" to end your procedure and press "F5" to run the VBA code.

Related Searches


  • Photo Credit John Foxx/Stockbyte/Getty Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!