Return A Portion Of A String

Instead of an entire string, you can use the built-in functions available in VBA to return only a portion of a string. These functions work well when you only want a smaller portion of a string. You can use three different functions to return a portion of a string. The Left function returns the specified number of characters starting at the left side, or beginning, of the string. The Right function returns the specified number of characters starting at the right side, or end of the string. Each of these functions have the same two required arguments: Left(string, length) and Right(string, length).

The string argument specifies the string from which you want to return the specified number of characters. You can make the argument an actual string enclosed in quotes, a variable that contains a string, or a cell reference. The length argument indicates the number of characters to return from the string.

The third built-in function for returning a portion of a string is the Mid function. This function works well for retrieving characters from the center of a string. When you use this function you indicate the first character with which to start and how many characters to return. There are three different arguments for the Mid function: Mid(string, start, length).

Similiar to the Left and Right functions, the Mid function string argument specifies the string to use with the function. The start argument indicates the position of the first character in the string to return. The length argument is the only optional argument with the Mid function. If you omit the length argument, the function returns the remaining portion of the string. Otherwise, the length argument indicates the number of characters to return.

RETURN A PORTION OF A STRING

RETURN A PORTION OF A STRING

USING THE LEFT/RIGHT FUNCTION_

0 Switch to Excel and run the associated macro.

USING THE LEFT/RIGHT FUNCTION_

-D Create a new subroutine.

ype Dim StringVar As String, replacing StringVar with the string variable.

< Type StringVar = "String"

replacing "String" with the string to assign to the variable.

'-Q Type Result = Left(StringVar,

#), replacing Result with the shortened string and # with the number of characters.

■ To return characters from the right side of the string, you can replace Left with Right.

0 Switch to Excel and run the associated macro.

-■ The function returns the shortened string.

0 0

Post a comment