Wednesday, February 10, 2016

Excel/Access VBA String Functions


Excel/Access VBA String Functions
Here are the VBA codes for manipulating strings.
            • Left Function
            • Right Function
            • Mid Function
            • Len Function
            • Replace Function

LEFT Function
  • Cut the text starting from the first character from the left to the specified number of characters to the right.

     Syntax: LEFT(text,  numberofchars)
Example:

Function leftStr()       'returns "Hello"
Dim str, leftString As String

str = "Hello World!”
leftString = Left(str, 5)
MsgBox leftString
End function
                    

RIGHT Function
  • Cut the text starting from the first character from the right to the specified number of characters to the left.

     Syntax: RIGHT(text, numberofchars)
Example:

Function rightStr()     'returns "World!"
Dim str, rightString As String

str = "Hello World!”
rightString = Right(str, 6)
MsgBox rightString
End function
         

MID Function
  • Cut the text starting from specified starting character from the left, to the specified number of characters to the right.

     Syntax: MID(text, startofchar, numberofchars).
Example:

Function midStr()       'returns "lo Wor"
Dim str, midString As String

str = "Hello World!”
midString = Mid(str, 3, 6)
MsgBox midString
End function
            

LEN Function 
  • Get the length or the number of characters the text contains.

Syntax: LEN(text).
Example:                                             

Function lenStr()        'returns 12
Dim str, lenString As String

str = "Hello World!”
lenString = Len(str)
MsgBox lenString
End function


REPLACE Function
  • Replaces the specified found string with the specified new string.

     Syntax: REPLACE(text, stringtoreplace, replacewith).
Example:

Function replaceStr()           'returns “Hi World!”
Dim str, replaceString As String

str = "Hello World!”
replaceString = Replace(str, “Hello”, ”Hi”)
MsgBox replaceString
End function

No comments:

Post a Comment