VBA basics
47. VBA function (String operation, Replace, InStr, StrConv)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-15-2019

47. VBA function (String operation, Replace, InStr, StrConv)

Some of this topic was processed by machine translation. Original


String manipulation is essential for programming with macro VBA,
Data shaping, data cleansing, data cleaning,
An essential part of VBA that does this is the string manipulation function.


Here is a list of VBA functions related to string manipulation and
This section explains the Replace function, InStr function, and StrConv function.
For other functions, please refer to the individual pages from the links in the list.

All VBA function list is VBA function list

List of VBA functions related to string operations

The link in the table below opens the VBA function description page.
Bold text is also explained at the bottom of this page.



Function Description Supported worksheet functions
Asc A conversion function that returns the character code of the first character in the specified string Same as CODE of worksheet function
Chr Returns a string value indicating the character corresponding to the specified character code Same as CHAR of worksheet function
LCase Convert uppercase letters of the alphabet to lowercase Same as LOWER of worksheet function
UCase Convert lower case letters to upper case Same as UPPER of worksheet function
Len Returns a value representing the number of characters in the specified string or the number of bytes required for the specified variable Same as worksheet function
Left Returns a string of the specified number of characters from the left end of the string Same as worksheet function
Mid Returns a string for a specified number of characters from a string Same as worksheet function, number of characters can be omitted
Right Returns a string of the specified number of characters from the right end of the string Same as worksheet function
Replace Returns a string obtained by replacing part of the specified string with the number of times specified by another string Same as SUBSTITUTE of worksheet function
InStr Searches for a specified string from a string and returns the first character position found Same as worksheet function FIND
However, case sensitivity can be specified by compare.
InStrRev A string processing function that starts searching from the last character position for a specified character string within a string and returns the first character position found.  
Space A string processing function that returns a string consisting of a specified number of spaces  
String This is a character string processing function that returns a character string with the specified character code or the first character of the character string arranged for the specified number of characters. Same as REPT of worksheet function
Str A string processing function that returns the value of a formula as a string value (number)
StrConv Returns the converted string as a variant type (variant of internal processing format String)  
StrReverse Returns a string that reverses the sequence of characters in the specified string  
Trim Returns a value that represents a string with both leading and trailing spaces removed from the specified string Same as worksheet function, but no intermediate spaces are removed
LTrim Returns a value representing a string with leading spaces removed from the specified string  
RTrim Returns a value representing a string with trailing spaces (RTrim) removed from the specified string  
Val Returns the number contained in the specified string converted to an appropriate data type It is close to the VALUE of the worksheet function, but only the first numerical part is taken out

If you can't figure out the corresponding worksheet function, remember that first.
Even if it is described as the same as the worksheet function, it is not exactly the same.
Note that there are some differences in arguments and return values.
Here are some of the frequently used macros that you need to explain.
The table above is bold.

Replace function

Returns a string in which a part of the specified string is replaced by the number of times specified by another string.

Replace(expression, find, replace[, start[, count[, compare]]])

expression Must be specified.
Specify a string expression that contains the string to replace.
find Must be specified.
Specify the character string to search for.
replace Must be specified.
Specify the character string to be replaced.
start It can be omitted.
Specifies the search start position of the internal string in the argument expression.
If this argument is omitted, 1 is used. .
count It can be omitted.
Specify the number of character strings to be replaced. If this argument is omitted, the default value of -1 is used and all candidates are replaced.
compare It can be omitted.
Specifies a number that represents the mode of string comparison to use when evaluating string expressions. For the values to be set, see “Setting values” below.

Example of using the Replace function
Replace("123aAbBcC","A","Z") → "123aZbBcC"
Reference for actual battle VBA code
Please refer to the page of VBA function list below.

InStr function

This is a character string processing function that searches a specified character string from a character string and returns the first character position found.

InStr([start, ]string1, string2[, compare])



start It can be omitted.
Specifies a formula that represents the starting position of the search.
If omitted, the search starts from the first character. An error occurs if the argument start contains a null value.
If you specify the compare argument, you must also specify start.
string1 Must be specified.
Specify the string expression to be searched.
string2 Must be specified.
Specifies the string expression to search for in the argument string1.
compare It can be omitted.
Set the number that specifies the comparison mode for string comparison.
An error occurs if the compare argument is null.
If the argument compare is specified, the argument start must also be specified.
If the compare argument is omitted, the comparison mode is determined according to the setting of the Option Compare statement.
To use a local comparison rule, specify a valid LCID (LocaleID).

*Note
You cannot use named arguments with this function.
If specified, a compilation error will occur.

Example of using the InStr function
InStr("123aAbBcC","A") → 5

Please refer to the page of VBA function list below.

StrConv function

Returns a string converted by the specified conversion method.

StrConv(string, conversion, LCID)

Part Description
string Required.String expression?to be converted.
conversion Required.Integer. The sum of values specifying the type of conversion to perform.

vbUpperCase 1 Converts the string to uppercase characters.
vbLowerCase 2 Converts the string to lowercase characters.
vbProperCase 3 Converts the first letter of every word in a string to uppercase.
vbWide* 4* Converts narrow (single-byte) characters in a string to wide (double-byte) characters.
vbNarrow* 8* Converts wide (double-byte) characters in a string to narrow (single-byte) characters.
vbKatakana** 16** Converts Hiragana characters in a string to Katakana characters.
vbHiragana** 32** Converts Katakana characters in a string to Hiragana characters.
vbUnicode 64 Converts the string to?Unicode?using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode 128 Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)
LCID Optional. The LocaleID, if different than the system LocaleID. (The system LocaleID is the default.)

*Applies to East Asia locales. **Applies to Japan only.

Examples of using StrConv;
StrConv("123aAbBcC",vbUpperCase) → "123AABBCC"
StrConv("123aAbBcC",vbLowerCase) → "123aabbcc"
StrConv("123aAbBcC",vbWide) → "123aAbBcC"

Please refer to the page of VBA function list below.

At the end of the string function

I explained here separately from the VBA function list.
Replace function, InStr function, StrConv function
These functions are frequently used in macro VBA and become important and essential to learn.



Articles with the same theme "VBA basics"

44. About VBA functions
45. VBA function (Format)
46. VBA function (date, DateAdd)
47. VBA function (String operation, Replace, InStr, StrConv)
48. VBA function (Other, Fix, Int, Rnd, Round, IsEmpty)
49. Like operator and wildcard
50. General practice question 6
51. With statement
52. Object variables and Set statements
53. Workbook object
54. Window object



  • >
  • >
  • 47. VBA function (String operation, Replace, InStr, StrConv)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text