Last Updated on February 8, 2023 by token
VBA character functions in Excel
The Excel VBA offers us a number of useful character functions. We can’t compare them to the number of functions in an Excel spreadsheet, and these are not the same functions. The use of the sheet functions in VBA code will be described in other chapter.
VBA Character functions can be combined. You can also nest one function in another. The table listed below contains all of the character functions in VBA and their detailed description.
VBA character functions – list of functions
| ASC (string) | The function replaces the given character in the argument with the next character number in the ASCII code |
| CHR (string) | Inverse of the ASCII function. Converts the next numeric character to a character in the ASCII code. |
| INSTR (string_1, string_2) | The function searches in string_2 for the phrase contained in string_1, returning its position in the form of a number / place, which it occupies in turn. It starts from the left side. He is looking for the first appearance |
| INSTRREV (string_1, string_2) | The function searches for string_2 in the string string_1, returning its position in the form of a number / space that it occupies in turn. It starts from the right side.He is looking for the first appearance |
| LCASE (string) | The function converts all letters in a string to lowercase letters. |
| LEFT (string, int) | The function returns the selected number of characters from the text string starting from the left side. |
| LEN (string) | The function returns the number of characters in the given string. |
| LTRIM (string) | The function deletes the spaces on the left side of the text string. |
| MID (string, int_1, int_2) | The function excludes from the sequence string a string starting in position int_1 with length int_2. |
| REPLACE (string_1, string_2, string_3) | The function converts the characters given in the argument string_2 into the string given in the argument string_3 in the string string_1. We can change both a single character and entire phrases. |
| RIGHT (string, int) | The function returns the selected number of characters from the text string starting from the right side. |
| RTRIM (string) | The function deletes spaces on the right side of the text string. |
| SPACE (int) | The function returns the number of spaces specified in the argument |
| SPLIT (string, delimiter) | The function searches the string ending the delimiter in string. Then, it deletes all text behind this character. |
| STR (number) | Converts a number to the String character type |
| STRCOMP (string_1, string_2, [comparison type]) | A function used to compare two text values. Compares the value of string_1 with the value of string_2. If string_1 is shorter than string_2, the function returns -1. If they are equal in length, they will return 0. If string_1 is longer, the function returns the value 1. |
| STRCONV (string, conversion) | The function used to convert text values. Changes the text value depending on the second argument we chose. The function is described in more detail in the chapter “Format and conversion functions” |
| STRREVERSE (string) | Returns a string in reverse order, starting from the end of the string. |
| TRIM (string) | The function removes spaces from the right and left of the text string. |
| UCASE (string) | The function converts all letters in a string to uppercase. |
| VAL (string) | The function searches all numeric characters in the text string, then returns them.It can be used to remove leading zeros from our text string. |
Some VBA character functions like STRCOMP () or SPLIT () allow you to select additional parameters that are not described above. I will describe them in detail in the advanced section.
An example of using character functions
Below are examples of using all of the vba character functions described in this chapter. As in the previous chapter, we create a macro that displays all the functions in the column “A” of the data sheet.
Sub FunExample()
Range("A1").Value = Asc("a")
Range("A2").Value = Chr(122)
Range("A3").Value = InStr("excel vba tutorial", "a")
Range("A4").Value = InStrRev(" excel vba tutorial", "a")
Range("A5").Value = LCase("Excel vba tutorial")
Range("A6").Value = Left("Excel vba tutorial", 5)
Range("A7").Value = Len("Excel vba tutorial")
Range("A8").Value = LTrim(" Excel vba tutorial ")
Range("A9").Value = Mid(" Excel vba tutorial ", 10, 8)
Range("A10").Value = Replace("Excel vba tutorial", "Excel", "tutorial")
Range("A11").Value = Right("Excel vba tutorial", 9)
Range("A12").Value = RTrim(" Excel vba tutorial ")
Range("A13").Value = Space(5) + "After this text put 5 spaces"
Range("A14").Value = Split("Excel vba tutorial", " ")
Range("A15").Value = Str(123)
Range("A16").Value = StrComp("Excel VBA", " Course", vbTextCompare)
Range("A17").Value = StrConv("excel vba tutorial", vbUpperCase)
Range("A18").Value = StrReverse("Excel vba tutorial")
Range("A19").Value = Trim(" Excel vba tutorial ")
Range("A20").Value = UCase("Excel vba tutorial")
Range("A21").Value = Val("0123")
End Sub
Excel file with an example I put below:
Tasks (You can enter the solution in a comment)
4.1. Acting on the phrase “VBA character functions” do the following actions using text functions: count the length of the string; convert all letters to uppercase letters; replace the VBA phrase with the VisualBasic phrase; cut 5 characters from the text starting from the left side.
4.2 Using the InputBox window, enter a text variable of 004535 into VBA. Then change it to a number and use the mathematical function to extract the root element from it.
