User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Excel VBA String Functions:

 

Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE, InStr & InStrRev Functions

 

-------------------------------------------------------

Contents:

LEFT Function (Worksheet / VBA)

RIGHT Function (Worksheet / VBA)

MID Function (Worksheet / VBA)

LEN Function (Worksheet / VBA)

REPLACE Function (Worksheet)

REPLACE Function (VBA)

InStr & InStrRev Functions (VBA)

-------------------------------------------------------

 

In Excel vba, a String refers to a sequence of contiguous characters within quotation marks viz. "This is a string expression within quotation marks, in vba." These characters are literally interpreted as characters, in the sense that these represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. A string expression can have as its elements - a string of contiguous characters, a function that returns a string, a string variable, a string constant or a string variant. This section does a detailed discussion on using Excel VBA String Functions to manipulate text strings with vba code. Also refer related link: Excel VBA String Functions: SPLIT, JOIN, CONCATENATE.

 

 

LEFT Function (Worksheet / VBA)

 

The Excel LEFT function can be used both as a worksheet function and a VBA function. The LEFT function returns the specified number of characters in a text string, starting from the first or left-most character. Use this function to extract a sub-string from the left part of a text string. Syntax: LEFT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the LEFT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.

 

 

RIGHT Function (Worksheet / VBA)

 

The Excel RIGHT function can be used both as a worksheet function and a VBA function. The RIGHT function returns the specified number of characters in a text string, starting from the last or right-most character. Use this function to extract a sub-string from the right part of a text string. Syntax: RIGHT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the RIGHT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null.

 

 

MID Function (Worksheet / VBA)

 

The Excel MID function can be used both as a worksheet function and a VBA function. The MID function returns the specified number of characters in a text string, starting from a specified position (ie. starting from a specified character number). Use this function to extract a sub-string from any part of a text string. Syntax: MID(text_string, start_number, char_numbers). The text_string argument is the text string from which you want to extract the specified number of characters. The start_number argument specifies the character number from which to start extracting the sub-string, the first character in a text string being start_number 1 and incrementing towards the right. The char_numbers argument specifies the number of characters to extract from the text string.

 

If start_number is greater than the length of the text string, an empty string (zero length) is returned; if it is less than the length of the text string but together with char_numbers (ie. start_number PLUS char_numbers) it is greater than the length of the text string, the MID function will return the text string in full from the start_number position to the end of the text string.

 

Using MID function as a worksheet function, if a negative value is specified for char_numbers, MID will return the #VALUE! error value; if start_number is less than 1, MID will return the #VALUE! error value. All arguments are necessary to be specified when using as a worksheet function.

 

Using MID function as a VBA function: The char_numbers argument is optional when used as VBA function, and if omitted the function will return the text string in full from the start_number position to the end of the text string. All other arguments are necessary to be specified when using as a vba function. If text_string contains Null, the function also returns Null.

 

 

LEN Function (Worksheet / VBA)

 

The Excel LEN function can be used both as a worksheet function and a VBA function. The worksheet LEN function returns the number of characters in a text string. Use this function to get the length of a text string. Syntax: LEN(text_string). It is necessary to mention the text_string argument which is the text string whose length you want to get in number of characters. Note that spaces also count as characters. The worksheet LENB function returns the number of bytes used to represent the characters in a text string - counts each character as 1 byte except when a DBCS language [viz. Japanese, Chinese (Simplified), Chinese (Traditional), and Korean] is set as the default language wherein a character is counted as 2 bytes. Syntax: LENB(text_string).

 

While using LEN as a VBA function - Syntax: Len(text_string) or Len(variable_name) - you can use either a text string or a variable name, and the function will return a Long value representing the number of characters contained in the string or the number of bytes required to store a variable. Using the vba Len function for a variable of type variant will treat the variable as a String and return the number of characters contained in it. A text_string or variable containing Null, will also return Null. The vba Len function returns the number of characters in the string where the variable is of subtype String or Variant, and wherein the variable is of subtype numeric the function returns the number of bytes used to store the variable.

 

 

Example - Using Left, Right, Mid & Len functions in vba code.

 

Sub Left_Right_Mid_Len()
'using vba Left, Right, Mid & Len functions.

 

Dim str As String, strLeft As String, strRight As String, strMid As String

 

str = "James Bond"

 

strLeft = Left(str, 7)

'returns "James B", which are the first 7 characters (space is counted as a distinct character).

MsgBox strLeft

 

strLeft = Left(str, 15)

'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.

MsgBox strLeft

 

strRight = Right(str, 7)

'returns "es Bond", which are the last 7 characters (space is counted as a distinct character).

MsgBox strRight

 

strRight = Right(str, 15)

'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters.

MsgBox strRight

 

strMid = Mid(str, 2, 6)

'Returns "ames B". Starts from the second character ie. "a", and then specifies that 6 characters be returned starting from "a".

MsgBox strMid

 

strMid = Mid(str, 2, 15)

'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the specified characters of 15 plus start number 2 (ie. total of 17) exceed the string length of 10 characters.

MsgBox strMid

 

strMid = Mid(str, 2)

'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the second argument (char_numbers) is omitted.

MsgBox strMid

 

strMid = Mid(str, 12, 2)

'Returns an empty string (zero length), because the start number of 12 exceeds the string length of 10 characters.

MsgBox strMid

 

'Returns 10, the string length measured by its number of characters.

MsgBox Len(str)

 

'Returns 10, the string length measured by its number of characters.

MsgBox Len("James Bond")

 

End Sub

 

 

 

Example - Using the vba Len function - variable types.

 

Sub Len_vbaFunc()
'using vba Len function - variable types


'---------------------------

'returns 3 in both cases - number of characters in the string:

MsgBox Len("bad")

MsgBox Len("245")

 

'returns 10 - number of characters in the string including the space:

MsgBox Len("James Bond")

 

'---------------------------

'a variable of type variant is treated as a string

Dim vVar As Variant

vVar = 245

'returns 2, indicating variable subtype Integer:

MsgBox VarType(vVar)

'Returns 3, the number of characters contained in the variable - the Len functions treats the variant variable as a String:

MsgBox Len(vVar)

 

'---------------------------

'a variable of type string

Dim strVar As String

strVar = "James Bond"

'returns 8, indicating variable subtype String:

MsgBox VarType(strVar)

'Returns 10, the number of characters contained in the variable of type String:

MsgBox Len(strVar)

 

'---------------------------

'a variable of type integer

Dim iVar As Integer

iVar = 245

'Returns 2, the number of bytes used to store the variable:

MsgBox Len(iVar)

 

'a variable of type long

Dim lVar As Long

lVar = 245

'Returns 4, the number of bytes used to store the variable:

MsgBox Len(lVar)

 

'a variable of type single

Dim sVar As Single

sVar = 245.567

'Returns 4, the number of bytes used to store the variable:

MsgBox Len(sVar)

 

'a variable of type double

Dim dVar As Double

dVar = 245.567

'Returns 8, the number of bytes used to store the variable:

MsgBox Len(dVar)

'---------------------------


End Sub

 

 

 

Example - Using LEN and MID Functions, to determine characters appearing at odd number positions in a text string.

 

Sub Mid_Len_OddNoCharacters()
'Using LEN and MID Functions, to determine characters appearing at odd number positions in a text string
 
Sheets("Sheet1").Activate
Dim str As String, strOdd, i As Integer
'assign text string in cell A2 ("HELLO") to variable (str)
str = ActiveSheet.Range("A2").Value
'loop though each character - vba Len function determines the length or number of characters in the text string
For i = 1 To Len(str)
'check odd number position
If i Mod 2 = 1 Then
'return character at odd number position, and add it to the string variable srtOdd
'vba Mid function extracts 1 character from str, starting from character number i
strOdd = strOdd & Mid(str, i, 1)
End If
Next
MsgBox strOdd
'enter the string (variable srtOdd) containing odd positioned characters in cell A3 ("HLO")
Range("A3").Value = strOdd

End Sub
 

 

 

 

Example - Using LEFT, LEN and MID Functions, to return initials of full name.

 
Sub Left_Mid_Len_InitialsOfName()
'Using LEFT, LEN and MID Functions, to return initials of full name.
'return initials from a text string containing full name comprising of multiple words
'consider a string containing the first name, middle name(s) & surname, having inbetween spaces - return initials of full name, wherein each initial is followed by a dot and space.

Sheets("Sheet1").Activate
Dim strName As String, strInitials As String
Dim i As Integer
'assign variable strName to the text string - the first name, middle name(s) & surname with inbetween spaces:
'strName = "   Alec Thomas stone     Hanson    "
strName = ActiveSheet.Range("A6").Value

'loop though each character:
For i = 1 To Len(strName)
'if first character
If i = 1 Then
'if the first character is not blank space, it will be an initial:
If Left(strName, i) <> " " Then
'add dot after first initial
strInitials = Left(strName, 1) & "."
End If
'if NOT first character
Else
'if any character after the first character is preceded by a blank space, it will be an initial:
If Mid(strName, i - 1, 1) = " " And Mid(strName, i, 1) <> " " Then
'determines if first initial
If Len(strInitials) < 1 Then
'add dot after first initial
strInitials = Mid(strName, i, 1) & "."
'for multiple initials:
Else
'for multiple initials, add to the previous initial(s):
strInitials = strInitials & " " & Mid(strName, i, 1) & "."
End If
End If
End If
Next i

'convert all initials to upper case:
strInitials = UCase(strInitials)
'returns "A. T. S. H." and enter the string variable strInitials in cell A7
MsgBox strInitials
Range("A7").Value = strInitials
'returns 11 - 4 letters, 4 dots & 3 blank spaces
MsgBox Len(strInitials)

End Sub
 
 

 

REPLACE Function (Worksheet)

 

The worksheet REPLACE function replaces part of a text string with a new text string, based on specified number of characters and starting from a specified position. Syntax: REPLACE(old_text, start_number, number_of_chars, new_text). It is necessary to specify all arguments. The old_text argument is the text string in which you want to replace with new text. The start_number argument is the position of the character in old_text, which you want to replace (ie. position of the first character from which replacement should start). Position is the character number, first character being number 1 & incrementing towards the right. The number_of_chars is the number of characters which will be replaced in the old_text (with new_text). The new_text is the text string which will replace the characters in old_text.

 

 

Example - using the Worksheet Replace Function in vba code to delete blank spaces in excess of a specified number, within a string

 
Function DeleteBlankSpaces(str As String, iSpaces As Integer) As String
'using the Worksheet Replace Function in vba code to delete blank spaces in excess of a specified number, within a string (str)
'use this code to reduce multiple spaces within a string to a specified number of spaces (iSpaces)
'this code can also delete ALL spaces within a string
'this code can also convert multiple spaces to a single space within a string, similar to the worksheet Trim function, except that using Trim deletes ALL blank spaces before the first (non-space) character also.

Dim n As Integer, counter As Integer
counter = 0

'start from last character in the string and move to the first
For n = Len(str) To 1 Step -1
'if character is blank space
If Mid(str, n, 1) = " " Then
'increment counter by 1
counter = counter + 1
'if blank space(s) equal to or less than specified number
If counter < iSpaces + 1 Then
'go to next character ie. next n
GoTo skip
'if blank space(s) in excess of specified number, then delete
Else
'using the worksheet Replace function to replace with a zero-length string
str = Application.Replace(str, n, 1, "")
End If
'if character is NOT blank space, go to next character ie. next n
Else
'restore to 0 if non-blank character
counter = 0
End If
skip:
Next n

'function returns the final string after deleting excess consecutive blank spaces
DeleteBlankSpaces = str

End Function
 
 
Sub DelBlSp()
'delete excess consecutive blank spaces within a string (str) ie. in excess of a specified number
'Note: this code does not ensure uniform no. of blank spaces but ONLY deletes excess blanks

Sheets("Sheet1").Activate
Dim strText As String, iSpaces As Integer

'assign string in cell A10 (" Specify    max spaces    in  text   .") of active sheet to variable (strText)
strText = ActiveSheet.Range("A10").Value
'specify the maximum number of consecutive spaces to retain within the string - this procedure will delete spaces in excess of this specified Number
'assign this max number to variable iSpaces
iSpaces = 2

'strText contains 4 blank spaces after "Specify", 4 blank spaces after "spaces" & 3 blank spaces after "text", all of which are reduced to 2 blank spaces
'enter final string in cell A11 (" Specify  max spaces  in  text  .") of active sheet - call DeleteBlankSpaces function and pass the arguments of string (strText) & specified blank spaces (iSpaces)
ActiveSheet.Range("A11").Value = DeleteBlankSpaces(strText, iSpaces)

End Sub
 
 
 
Example - ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present
 
 
Function UniformBlankSpaces(str As String, iSpaces As Integer) As String
'ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present

Dim n As Integer, counter As Integer
'counter is used to determine the no. of consecutive blank spaces
counter = 0

'start from last character in the string and move to the first
For n = Len(str) To 1 Step -1
'if character is blank space
If Mid(str, n, 1) = " " Then
'increment counter by 1
counter = counter + 1
'if blank space(s) is less than specified number
If counter < iSpaces Then
'if first character of the string is a blank space
If n = 1 Then
'add blank space
str = Left(str, 1) & " " & Right(str, Len(str) - n)
'if character preceding blank space at n is NOT a blank Space
ElseIf Mid(str, n - 1, 1) <> " " Then
'add blank space
str = Mid(str, 1, n) & " " & Right(str, Len(str) - n)
'if character preceding blank space at n is also a blank Space
Else
'go to next character ie. next n
GoTo skip
End If
'if blank space(s) in excess of specified number, then delete
ElseIf counter > iSpaces Then
'using the worksheet Replace function to replace with a zero-length string
str = Application.Replace(str, n, 1, "")
End If
'if character is NOT blank space, go to next character ie. next n
Else
'restore counter to 0 if non-blank character
counter = 0
End If
skip:
Next n

'function returns the final string with uniform consecutive blank spaces
UniformBlankSpaces = str

End Function
 
 
Sub UniformBlSp()
'ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present
'Note: this code ensures uniform no. of blank spaces where lead & trailing spaces are also present

Sheets("Sheet1").Activate
Dim strText As String, iSpaces As Integer

'assign string in cell A10 (" Specify    max spaces    in  text   .") of active sheet to variable (strText)
strText = ActiveSheet.Range("A10").Value
'specify the uniform number of consecutive spaces to retain within the string - this procedure will delete/add spaces in excess/short of this specified Number
'assign this number to variable iSpaces
iSpaces = 2

'enter final string in cell A12 ("  Specify  max  spaces  in  text  .") - call UniformBlankSpaces function and pass the arguments of string & specified blank spaces
ActiveSheet.Range("A12").Value = UniformBlankSpaces(strText, iSpaces)

End Sub
 
 
 
 

REPLACE Function (VBA)

 

The vba Replace Function is used to return a string in which a specified substring is replaced, a specified number of times, with another specified substring. Syntax:  Replace(expression, find, replace, start, count, compare). It is necessary to specify the arguments of expression, find and replace, while the start, count & compare arguments are optional.

 

The expression argument is the string in which a specific substring is replaced. For a zero-length expression string a zero-length string is returned, and for a Null expression the function will give an error. The find argument specifies the substring which is to be replaced. If find substring is zero-length, then a copy of the expression is returned. The replace argument specifies that 'another substring' which replaces (ie. the replacement substring). A replace substring of zero-length has the effect of deleting all occurrences of find from the expression. The start argument specifies the position (ie. character number) in the expression from where you want to start your search for the 'find' substring. If omitted, it will default to1 (ie. search will start from first character position). The string returned by the Replace function begins from this start position till the last character of the expression string, after the replacement(s). Specifying a start position which is greater than the length of the expression, will return a zero-length string. The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements. Specifying zero for the count will have the effect of no replacement and will return a copy of the expression. The compare argument specifies the type of comparison to use for evaluating substrings - a numeric value or a constant can be specified herein, as detailed below.

 

You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.

 

 

 

Example - using the vba Replace function.

 

Sub Replace_vbaFunc()
'using the vba Replace function

 

Dim str As String, strFind As String, strReplace As String

'find all ocurrences of "s" to be replaced:

strFind = "s"

'set replace string as zero-length:

strReplace = ""

 

'---------------------------

str = "She was selling sea shells!"

'returns 27:

MsgBox Len(str)

'returns the string after deleting all occurences of 's' - "She wa elling ea hell!". Note that capital 'S' is not replaced.

str = Replace(str, strFind, strReplace)

MsgBox str

'returns 22:

MsgBox Len(str)

 

'---------------------------

str = "She was selling sea shells!"

'returns 27:

MsgBox Len(str)

'returns the string after deleting all occurences of 's' - "he wa elling ea hell!".

'Note that capital 'S' is also replaced because a textual comparison has been done by setting the compare argument value to vbTextCompare (value: 1).

str = Replace(str, strFind, strReplace, , , 1)

MsgBox str

'returns 21:

MsgBox Len(str)

 

'---------------------------

str = "She was selling sea shells!"

'returns 27:

MsgBox Len(str)

'deleting all occurences of 's' from start position 8 - returns " elling ea hell!". Note that the character number 8 is a blank space and this is also returned.

'the string returned by the Replace function begins from this start position till the last character of the expression string.

str = Replace(str, strFind, strReplace, 8)

MsgBox str

'returns 16:

MsgBox Len(str)

 

'---------------------------

str = "She was selling sea shells!"

'returns 27:

MsgBox Len(str)

'Specifying a start position which is greater than the length of the expression, will return a zero-length string.

str = Replace(str, strFind, strReplace, 28)

'returns a zero-length string:

MsgBox str

'returns 0:

MsgBox Len(str)

 

'---------------------------

str = "She was selling sea shells!"

'returns 27:

MsgBox Len(str)

'The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements.

'specifying a start position of 8, and count of 2:

str = Replace(str, strFind, strReplace, 8, 2)

'returns " elling ea shells!", after deleting 's' twice from start position of 8.

MsgBox str

'returns 18:

MsgBox Len(str)

'---------------------------


End Sub

 

 

 

Example - Manipulate Strings in Excel VBA - Remove Numericals, Delete/Add Blank Spaces, Capitalize letters.

 

To download Excel file with live code, click here.

 
Function StringManipulation(str As String) As String
'String Manipulation using vba & worksheet Replace funcions, Trim / Len / Right / Mid / Chr / Asc / Ucase function.
'This code manipulates a string text as follows:
'1. removes numericals from a text string;
'2. removes leading, trailing & inbetween spaces (leaves single space between words);
'3. adds space (if not present) after each exclamation, comma, full stop and question mark;
'4. capitalizes the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark;

Dim iTxtLen As Integer, iStrLen As Integer, n As Integer, i As Integer, ansiCode As Integer

'---------------------------
'REMOVE NUMERICALS

'The vba Chr function returns a character (string data type) identified to the specified character code.
'Excel uses ANSI character set for Windows computers. The Windows operating environment uses the ANSI character set, whereas Macintosh uses the Macintosh character set, so that the returned character corresponds to this character set.
'chr(48) to chr(57) represent numericals 0 to 9 in ANSI/ASCII Character codes
For i = 48 To 57
'remove all numericals from the text string using vba Replace function
str = Replace(str, Chr(i), "")
Next i

'---------------------------
'REMOVE LEADING, TRAILING & INBETWEEN SPACES (LEAVE SINGLE SPACE BETWEEN WORDS)

'use the worksheet TRIM function. Note: the TRIM function removes space character with ANSI code 32, does not remove the nonbreaking space character with ANSI code 160
str = Application.Trim(str)

'---------------------------
'ADD SPACE (IF NOT PRESENT) AFTER EACH EXCLAMATION, COMMA, DOT AND QUESTION MARK:

'set variable (iTxtLen) value to string length
iTxtLen = Len(str)
'start from last character in the string and move to the first
For n = iTxtLen To 1 Step -1
'Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns dot / full stop; Chr(63) returns question mark;
If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then
'check if space is not present after, except for the last Character
If Mid(str, n + 1, 1) <> Chr(32) And n <> iTxtLen Then
'using Mid & Right functions to add space - note that current string length is used
str = Mid(str, 1, n) & Chr(32) & Right(str, iTxtLen - n)
'update string length - increments by 1 after adding a space (character) - this enables correct use of the Right Function in above line
iTxtLen = iTxtLen + 1
End If
End If
Next n

'---------------------------
'DELETE SPACE (IF PRESENT) BEFORE EACH EXCLAMATION, COMMA, DOT & QUESTION MARK:

'reset variable value to string length:
iTxtLen = Len(str)
'exclude the first character
For n = iTxtLen To 2 Step -1
'Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns full stop; Chr(63) returns question mark;
If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then
'check if space is present before
If Mid(str, n - 1, 1) = Chr(32) Then
'using the worksheet Replace function to delete a space:
str = Application.Replace(str, n - 1, 1, "")
'omit rechecking the same character again- position of n shifts (decreases by 1) due to deleting a space character:
n = n - 1
End If
End If
Next n

'---------------------------
'CAPITALIZE LETTERS:

'capitalize the very first letter of the string and the first letter of a word after each exclamation, full stop and question mark, while all other letters are lower case
iStrLen = Len(str)
For i = 1 To iStrLen
'determine the ANSI code of each character in the string: Excel uses ANSI character set for Windows computers. The Windows operating environment uses the ANSI character set, whereas Macintosh uses the Macintosh character set, so that the returned character corresponds to this character set.
'The vba Asc function returns the corresponding character code (Integer data type) for the string letter - Asc(string).
ansiCode = Asc(Mid(str, i, 1))
Select Case ansiCode
'97 to 122 are the ANSI codes equating to small cap letters "a" to "z"
Case 97 To 122
'character no 3 onwards to enable determining if 2 positions preceding it is an exclamation, full stop & question mark
If i > 2 Then
'capitalizes a letter whose position is 2 characters after (1 character after, will be the space character added earlier) an exclamation, full stop & question mark:
If Mid(str, i - 2, 1) = Chr(33) Or Mid(str, i - 2, 1) = Chr(46) Or Mid(str, i - 2, 1) = Chr(63) Then
Mid(str, i, 1) = UCase(Mid(str, i, 1))
End If
'capitalize first letter of the string:
ElseIf i = 1 Then
Mid(str, i, 1) = UCase(Mid(str, i, 1))
End If
'if capital letter, skip to next character (ie. next i):
Case Else
GoTo skip
End Select
skip:
Next i

'---------------------------
'function returns the final manipulated string
StringManipulation = str
 
End Function
 
 
Sub Str_Man()
'manipulate string
Sheets("Sheet2").Activate
Dim strText As String
'specify the text string which is required to be manipulated (" 2manipulate    text string8 ,    by   vba   Code   .check   below cell  !    ok ?thanks 7 .   "), and assign to string variable strText
strText = ActiveSheet.Range("A2").Value
'enter manipulated string in cell A3 ("Manipulate text string, by vba Code. Check below cell! Ok? Thanks.") of active sheet - call StringManipulation function and pass the string (str) argument
ActiveSheet.Range("A3").Value = StringManipulation(strText)
End Sub
 
 
 

 

Example - Replace all occurrences of a substring in a string expression, with another substring - using the vba Replace function.

 

To download Excel file with live code, click here.

 
Function Replace_Str1(var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer) As Variant
'Replaces all occurrences of substring (vFind) in the string (var), with another substring (vReplace) - using the vba Replace function.

Dim iPosn As Integer
'position of the first occurrence of vFind, within var:
iPosn = InStr(var, vFind)

'if vFind not found within var
If iPosn < 1 Then
'return var string:
Replace_Str1 = var
'if vFind found within var
Else
'use the vba Replace function to replace all instances of vFind with vReplace
Replace_Str1 = Replace(var, vFind, vReplace, , , iOption)
End If

End Function
 
 
Sub ReplaceStr1()
'Replaces all occurrences of substring (vFind) in the string (var), with another substring (vReplace) - using the vba Replace function.
'this procedure calls Replace_Str1 function and passes the four argumentes of string (var) in which a specific substring (vFind) is replaced with another substring (vReplace) and the type of comparison (iOption) to use for evaluating substrings

Sheets("Sheet3").Activate
Dim var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer

'var is the string within which vFind is searched & replaced by vReplace:
'var = "she Sells sea shellS"
var = ActiveSheet.Range("A2").Value
'vFind is the string to search within var & which will be replaced by vReplace:
vFind = "s"
'vReplace is the string which replace all instances of vFind within var:
vReplace = "?"
'----------
'to delete all spaces, use a zero-length string for vReplace
'vFind = " "
'vReplace = ""
'----------
'in a case-insensitive comparison, if you specify to replace occurrences of "a" then instances of both "A" & "a" will be replaced.
'to perform a binary comparison (case-sensitive), use value 0:
'iOption = 0
'to perform a text comparison (case-insensitive), use value 1:
iOption = 1

'Null: where a variable contains no valid data - Null may have been explicitly assigned to a variable, or it could be a result of an operation between expressions containing Null.
'if var is Null, exit procedure
If IsNull(var) Then
MsgBox "var is Null, exiting procedure"
Exit Sub
'if var is not Null:
Else
'if vFind is Null or a zero-length string:
If IsNull(vFind) Or vFind = "" Then
'return var without any replacements & exit procedure:
MsgBox "Either vFind is Null or a zero-length"
ActiveSheet.Range("A3") = var
Exit Sub
Else
'if var or vFind are not Null, and vFind is not zero-length, replace all instances of vFind ("?he ?ell? ?ea ?hell?"):
ActiveSheet.Range("A3") = Replace_Str1(var, vFind, vReplace, iOption)
End If
End If

End Sub
 

 

 

InStr & InStrRev Functions (VBA)

 

The InStr Function returns the position (character number) in which a string first occurs within another string. Syntax: InStr(start, string, substring, compare). It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional.

 

The start argument specifies the position (ie. character number) within string from where you want to start your search for the substring. It is necessary to specify the start argument, if the compare argument is to be specified. If omitted, it will default to1 (ie. search will start from the first character position). Specifying a start position which is greater than the length of string will return a 0 (zero), and if start contains Null an error will occur. The string argument is the string expression within which substring is being searched. The function returns 0 if string is zero-length, and returns Null if string is Null. The substring argument is the string expression which is being searched within string and whose position will be returned by the function. The function returns 0 if substring is not found, returns the start value if string is zero-length, and returns Null if substring is Null. The compare argument specifies the type of comparison to use for evaluating strings - a numeric value or a constant can be specified herein, as detailed below.

 

You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary.

 

 

The InStrRev Function returns the position (character number) in which a string first occurs within another string, starting from the end of that another string. Syntax: InStrRev(string, substring, start, compare). Use InStrRev function instead of InStr to search in the reverse direction. It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional. If start is omitted, -1 is used, meaning that the search will begin from the last character position. All other syntax explanations remain same as in the InStr function.

 

 

 

Example - Using the InStr function.

 

Sub InStrFunc()
'using the InStr function

 

Dim str1 As String, str2 As String

 

'-----------------

str1 = "she sells sea shells"

str2 = "e"

'returns 3:

MsgBox InStr(str1, str2)

 

'-----------------

str1 = "she sells sea shells"

str2 = "e"

'returns 6:

MsgBox InStr(4, str1, str2)

 

'-----------------

str1 = "she sells sea shells"

str2 = "e"

'returns 0 because start value (24) is greater than length of str1 (20):

MsgBox InStr(24, str1, str2)

 

'-----------------

str1 = ""

str2 = "e"

'returns 0 because str1 is zero-length:

MsgBox InStr(str1, str2)

 

'-----------------

str1 = "she sells sea shells"

str2 = "f"

'returns 0 because str2 is not found:

MsgBox InStr(str1, str2)

 

'-----------------

Dim str3 As Variant

str1 = "she sells sea shells"

str3 = Null

'returns 1, indicating subtype Null - because str3 is Null:

MsgBox VarType(InStr(str1, str3))

 

'-----------------

str1 = "she Sells sea shells"

str2 = "s"

'returns 9 - the default text comparison type is Binary (case-sensitive):

MsgBox InStr(2, str1, str2)

'returns 5 - performing a textual comparison wherein comparison type is not case-sensitive:

MsgBox InStr(2, str1, str2, 1)

'-----------------


End Sub

 

 

 

Example - Replace all occurrences of a substring in a string expression, with another substring - using the InStr, Left & Mid functions.

 

To download Excel file with live code, click here.

 
Function Replace_Str2(var As Variant, vFind As Variant, vReplace As Variant) As Variant
'Replace all occurrences of a substring in a string expression, with another substring - using the InStr, Left & Mid functions.
'Replaces all occurrences of vFind in var, with vReplace - using the InStr, Left & Mid functions.
'Replacement will be case-sensitive in this procedure ie. if you specify to replace occurrences of "a" then instances of "A" will NOT be replaced.

Dim iFindLen As Integer
Dim iPosn As Integer

'position of the first occurrence of vFind, within var:
iPosn = InStr(var, vFind)
'length of vFind, which will be replaced with vReplace:
iFindLen = Len(vFind)
'length of vReplace, which will replaced vFind:
iReplaceLen = Len(vReplace)

'if vFind not found within var:
If iPosn < 1 Then
'return var string & exit procedure:
Replace_Str2 = var
Exit Function
'if vFind found within var:
Else
'loop:
Do
'replace vFind with vReplace within var - use the Left & Mid functions to return string. Omitting character nos from the Mid function will return the text string in full from start number position to end of the text string.
var = Left(var, iPosn - 1) & vReplace & Mid(var, iPosn + iFindLen)
'position of the first occurrence of vFind within updated var, starting the position search from the first character AFTER the latest replacement (iPosn + iReplaceLen)
iPosn = InStr(iPosn + iReplaceLen, var, vFind)
'if vFind not found within updated var, exit loop
If iPosn = 0 Then Exit Do
Loop
End If

'return final string:
Replace_Str2 = var

End Function
 
 
Sub ReplaceStr2()
'Replaces all occurrences of vFind in var, with vReplace - using the InStr, Left & Mid functions.
'Replacement will be case-sensitive in this procedure ie. if you specify to replace occurrences of "a" then instances of "A" will NOT be replaced.

Sheets("Sheet3").Activate
Dim var As Variant, vFind As Variant, vReplace As Variant

'var is the string within which vFind is searched & replaced by vReplace:
'var = "she Sells sea shellS"
var = ActiveSheet.Range("A6").Value
'vFind is the string to search within var & which will be replaced by vReplace
vFind = "s"
'vReplace is the string which replace all instances of vFind within var:
vReplace = "?"

'if var is Null, exit procedure:
If IsNull(var) Then
MsgBox "var is Null, exiting procedure"
Exit Sub
'if var is not Null:
Else
'if vFind is Null or a zero-length string:
If IsNull(vFind) Or vFind = "" Then
'return var without any replacements & exit procedure
MsgBox "Either vFind is Null or a zero-length"
ActiveSheet.Range("A7").Value = var
Exit Sub
Else
'if var or vFind are not Null, and vFind is not zero-length, replace all instances of vFind ("?he Sell? ?ea ?hellS")
ActiveSheet.Range("A7").Value = Replace_Str2(var, vFind, vReplace)
End If
End If

End Sub