Vba excel string functions. Examples of using VBA functions. String Operations

To simplify working with strings, there are a number of built-in functions that greatly facilitate operations such as converting strings to data of other types, finding a substring in a string, determining the length of a string, etc. In this article, we will consider the most common functions for working with strings.

1) The function for determining the length of a string LENGTH(string), returns the number of characters in a string, including trailing spaces.

SELECT LENGTH('string ') FROM DUAL will return the value 7.

2) Functions for converting character registers UPPER(string), LOWER(string), INITCAP(string). The UPPER() function is used to convert characters to uppercase.

SELECT UPPER('string') FROM DUAL will return STRING.

If it is necessary to convert the characters of a string to lower case, the LOWER() function is used.

SELECT LOWER('STrinG') FROM DUAL will return string.

The INITCAP function converts every first character of a word to uppercase and all other characters to lowercase, provided that the delimiter character between words is a space.

SELECT INITCAP('string1 string2') FROM DUAL will return the string String1 String2.

3) Functions for trimming leading and trailing spaces LTRIM(string), RTRIM(string), TRIM(string). Accordingly, the first function trims all initial spaces of the string, the second trims all trailing spaces, and the third trims all initial and trailing spaces.

SELECT LTRIM(' str1') FROM DUAL will return the string str1,
SELECT RTRIM('str2 ') FROM DUAL will return the string str2,
SELECT TRIM(‘ str3 ’) FROM DUAL will return the string str3.

4) The function of replacing part of a string with another string REPLACE(original_string, substring to be replaced, substring to be replaced). For greater clarity, consider an example, a number is stored in some text field of a table. Moreover, the separator character between the integer and fractional parts in some fields is “.”, and for further data processing we need it to be “,” in all fields. To do this, we use the REPLACE function as follows. REPLACE(field1, '.', ',') and all "." in the field field will be replaced by the symbol ",".

SELECT REPLACE('My_string','_','@') FROM DUAL will return a string [email protected].

5) Functions for converting data to other data types. TO_CHAR(number) converts a number to text. TO_NUMBER(string) converts text to a number. TO_DATE(string, dateformat) converts a string to a date of the specified format.

SELECT TO_CHAR(123) FROM DUAL will return row 123,
SELECT TO_NUMBER('12345') FROM DUAL will return the number 12345,
SELECT TO_DATE('01.01.2010','dd.mon.yyyy') FROM DUAL will return the date 01.JAN.2010.

6) The function for determining the occurrence of a substring in a string INSTR(source_string, substring, character_number). This function allows you to determine the character number in the source string from which the searched substring begins (if any). Otherwise, 0 is returned. For example, we need to determine all positions in the Table1 table, in the name of which the substring “manager” occurs. The following operator is quite suitable for this.

SELECT * FROM TABLE1 WHERE INSTR(POST, 'manager', 1) > 0.

That is, the SELECT statement will display only those records from the TABLE1 table where the searched substring "manager" will be found. Moreover, the search will be carried out from the first character. If the search is to be performed from a different position, then the character number to start the search is specified in the third parameter.

SELECT INSTR('Small string', 'string', 1) FROM DUAL will return the value 7,
SELECT INSTR('Small string', 'String', 1) FROM DUAL will return the value 0.

7) The function of extracting the substring SUBSTR (source_string, number_of_initial_symbol, number_of_characters) in the source string. Let's consider such an example, in the user table the address is stored in the form of the name of the settlement, the name of the street, the house number. Moreover, we know for sure that strictly 20 characters are allocated for the name of the settlement (if the name of the settlement is less than 20 characters, then the rest is filled with spaces), for the name of the street 30 characters, for the house number 3 characters. Next, we need to transfer all the addresses from our table to another and at the same time all 3 components of the address must be in different fields. To extract the components of the address, we use the SUBSTR() function.

SELECT SUBSTR(TABLE_1.ADDRESS, 1, 20) CITY, SUBSTR(TABLE_1.ADDRESS, 21, 30) STREET, SUBSTR(TABLE_1.ADDRESS, 52, 3) TOWN FROM TABLE_1

Of course, to transfer data, you must use the INSERT statement, but the example considered is quite suitable for understanding the operation of the SUBSTR function.
SELECT SUBSTR('My_string', 4, 3) FROM DUAL will return the string str.

The functions discussed above can be used in input parameters. So if we need to select all characters after a certain one, then the number of the desired character from the INSTR function can be passed to the SUBSTR function. For example, if you need to transfer all the characters from the table field that are located after the "," then you can use this construction
SELECT SUBSTR(My_string, INSTR(My_string, ',', 1), LENGTH(My_string)- INSTR(My_string, ',', 1)+1) FROM DUAL.
To determine the starting character, we call the INSTR() function, which will return the character number of the first occurrence of the substrings ",". Next, we define the number of characters to the end of the string as the difference between the length of the string and the number of the first occurrence of the substring.

8) To determine the character code, the ASCII(string) function is used, which returns the code of 1 character of the string. For example

SELECT ASCII(W) FROM DUAL will return the value 87.

9) Inverse function for converting a character code into a CHR (number) character.

SELECT CHR(87) FROM DUAL will return the character W.

Functions for working with numbers in Oracle.

Oracle has a number of functions for working with numbers. These include the functions raising a number to a power POWER(), rounding ROUND(), etc.

1) The ABS(number) function returns the absolute value of the argument.
SELECT ABS(-3) FROM DUAL will return the value 3.

2) The CEIL(number) function returns the smallest integer greater than or equal to the passed parameter.
SELECT CEIL(4.5) FROM DUAL will return the value 5.

3) The FLOOR(number) function returns the largest integer less than or equal to the passed parameter.
SELECT FLOOR(3.8) FROM DUAL will return the value 3.

4) The MOD(number_1, number_2) function returns the remainder of dividing the first parameter by the second.
SELECT MOD(5, 3) FROM DUAL will return 2. Note. If the second parameter is 0, then the function returns the first parameter.

5) Rounding function ROUND(number_1, number_2). Rounds the first argument passed to the number of digits passed in the second argument. If the second parameter is not specified, then it is taken equal to 0, that is, rounding is performed to an integer value. Examples
SELECT ROUND(101.34) FROM DUAL will return the value 101,
SELECT ROUND(100.1268, 2) FROM DUAL will return 100.13
SELECT ROUND(1234000.3254, -2) FROM DUAL will return 1234000,
SELECT ROUND(-100.122, 2) FROM DUAL will return -100.12.

6) The value truncation function TRUNC(number_1, number_2). Returns the value of the first parameter truncated to the number of decimal places specified in the second parameter. Examples
SELECT TRUNC(150.58) FROM DUAL will return the value 150
SELECT TRUNC(235.4587, 2) FROM DUAL will return 235.45
SELECT TRUNC(101.23, -1) FROM DUAL will return the value 100

7) Oracle DBMS has a number of trigonometric functions SIN(number), COS(number), TAN(number) and their inverses ACOS(number), ASIN(number), ATAN(number). They return the value of the trigonometric function corresponding to the name. For direct functions, the parameter is the value of the angle in radians, and for inverse functions, the value of the function. Examples
SELECT COS(0.5) FROM DUAL will return the value 0.877582561890373
SELECT SIN(0.5) FROM DUAL will return the value 0.479425538604203
SELECT TAN(0.5) FROM DUAL will return the value 0.546302489843791
SELECT ACOS(0.5) FROM DUAL will return the value 1.0471975511966
SELECT ASIN(0.5) FROM DUAL will return 0.523598775598299
SELECT ATAN(0.5) FROM DUAL will return the value 0.463647609000806

8) Hyperbolic functions. SINH(number),
COSH(number), TANH(number). SINH() returns the hyperbolic sine of the passed parameter, COSH() returns the hyperbolic cosine of the passed parameter, TANH() returns the hyperbolic tangent of the passed parameter. Examples
SELECT COSH(0.5) FROM DUAL will return the value 1.12762596520638
SELECT SINH(0.5) FROM DUAL will return the value 0.521095305493747 SELECT TANH(0.5) FROM DUAL will return the value 0.46211715726001

9) The exponentiation function POWER(number_1, number_2). Examples
SELECT POWER(10, 2) FROM DUAL will return 100
SELECT POWER(100, -2) FROM DUAL will return the value 0.0001

10) Logarithmic functions. LN(number) returns the natural logarithm of the passed parameter, LOG(number_1, number_2) returns the logarithm of the second passed parameter to the base given to the first parameter. Moreover, the first parameter must be greater than zero and not equal to 1. Examples
SELECT LN(5) FROM DUAL will return the value 1.6094379124341
SELECT LOG(10, 3) FROM DUAL will return the value 0.477121254719662

11) The function of extracting the square root SQRT(number). Example
SELECT SQRT(4) FROM DUAL will return the value 2.

12) The function of raising the number e to the power of EXP (number). Example
SELECT EXP(2) FROM DUAL will return 7.38905609893065.

Date Functions in Oracle

In practice, it is very often necessary to analyze data in the form of dates, perform some operations on them, change the format. All these operations are already implemented as built-in functions. Let's consider the most basic of them.

1) ADD_MONTHS(date, number_of_months) returns the date that is separated from the date passed in the first parameter by the number of months specified in the second parameter. Examples
SELECT ADD_MONTHS('01-JAN-2010', 2) FROM DUAL will return the date '03/01/2010'
SELECT ADD_MONTHS('01-JAN-2010', -3) FROM DUAL will return the date '10/01/2009'
SELECT ADD_MONTHS('30-JAN-2010', 1) FROM DUAL will return the date '28.02.2010'

2) The SYSDATE function is used to determine the current date and time. The scope of this function is much wider than it might seem at first glance. First of all, it is control over data entry into the database. In many tables, a separate field is allocated to store the date of the last modification. It is also very convenient to control certain input parameters for reports, especially if they should not be greater than the current date. In addition to the date, this function also returns the time to the nearest second. Example
SELECT SYSDATE FROM DUAL will return the date '22.05.2010 14:51:20'

3) If you need to determine the last day of the month, then the LAST_DAY (date) function is quite suitable for this. It can be used to determine the number of days left in a month.
SELECT LAST_DAY(SYSDATE) - SYSDATE FROM DUAL.
As a result of executing this statement, the number of days from the current date to the end of the month will be displayed. Example
SELECT LAST_DAY('15-FEB-2010') FROM DUAL will return the date '28.02.2010'.

4) Function to determine the number of months between dates MONTHS_BETWEEN(date_1, date_2). Examples
SELECT MONTHS_BETWEEN('01-JUL-2009', '01-JAN-2010') FROM DUAL will return -6
SELECT MONTHS_BETWEEN('01-JUL-2009', '10-JAN-2010') FROM DUAL will return -6.29032258064516.
Note. If the days of the months are the same, then the function returns an integer, otherwise the result will be a fraction, and the number of days in the month will be 31.

5) The NEXT_DAY(date, weekday) function allows you to determine the next date from the date passed in the first parameter, which corresponds to the day of the week passed in the second parameter. Example
SELECT NEXT_DAY('01-JUL-2009', 'mon') FROM DUAL will return the date '07/06/2009', i.e. the next Monday after July 1, 2009 is the 6th.

6) Rounding the date ROUND(date, format). The second parameter is optional, if it is not specified, then it is taken as 'DD', that is, rounding will be done to the nearest day. Examples
SELECT ROUND(SYSDATE) FROM DUAL will return the date '5/23/2010'
SELECT ROUND(SYSDATE, MONTH) FROM DUAL will return the date '06/01/2010', rounded up to the nearest first day of the month.

7) Date truncation. The TRUNC(date, format) function. As well as discussed above, it may not have a second parameter. In this case, truncation will be performed until the next day. Examples
SELECT TRUNC(SYSDATE) FROM DUAL will return the date '5/22/2010'
SELECT TRUNC(SYSDATE, 'WW') FROM DUAL will return the date '5/1/2010'
SELECT TRUNC(SYSDATE, 'Day') FROM DUAL will return the date '5/16/2010'.

Data Transformation Functions in Oracle

This section is devoted to the consideration of converting data into various formats. In practice, situations are quite common when it is necessary to treat string values ​​as numbers and vice versa. Despite the small number of functions, their capabilities are quite enough to solve very complex applied problems.

1) TO_CHAR(data, format). At first glance, the syntax is quite simple, but due to the second parameter, you can very accurately describe what format to convert the data into. So, both a date and a numeric value can be converted to a string. Consider the option of converting a date to a string. The values ​​of the most common formats are given in the table, more detailed information is contained in the technical documentation.

Table of format values ​​for converting a number to a string.

SELECT TO_CHAR(SYSDATE, 'D-MONTH-YY') FROM DUAL will return the string '7-MAY -10'
SELECT TO_CHAR(SYSDATE, 'DDD-MM-YYYY') FROM DUAL will return the string '142-05-2010'
SELECT TO_CHAR(SYSDATE, 'Q-D-MM-YYY') FROM DUAL will return the string '2-7-05-010'
SELECT TO_CHAR(1050, '9.99EEEE) FROM DUAL will return the string ' 1.050E+03'
SELECT TO_CHAR(1400, '9999V999') FROM DUAL will return the string '1400000'
SELECT TO_CHAR(48, 'RM') FROM DUAL will return the string ' XLVIII'

2) The function of converting a string into a date TO_DATE(string, format). Possible format values ​​have already been discussed above, so I will give a few examples of using this function. Examples
SELECT TO_DATE('01/01/2010', 'DD.MM.YYYY') FROM DUAL will return the date '01/01/2010'
SELECT TO_DATE('01.JAN.2010', 'DD.MON.YYYY') FROM DUAL will return the date '01.01.2009'
SELECT TO_DATE('15-01-10', 'DD-MM-YY') FROM DUAL will return the date '1/15/2010'.

3) The function of converting a string into a numeric value TO_NUMBER(string, format). The most common format values ​​are listed in the table, so let's consider the use of this function using examples. Examples
SELECT TO_NUMBER('100') FROM DUAL will return the number 100
SELECT TO_NUMBER('0010.01', '9999D99') FROM DUAL will return the number 10.01
SELECT TO_NUMBER("500,000","999G999") FROM DUAL will return the number 500000.

(http://www.cyberforum.ru/vba/thread638743.html)

Option 1

Option

You can also use a byte array:

Sub n() Dim Mass() As Byte, n As Long, Zam As String, TXT As String TXT = "=""DMITRY KOROTEEV" Mass = StrConv(TXT, vbFromUnicode) For n = 0 To UBound(Mass) If mass(n)<>34 And Mass(n)<>61 Then Zam = Zam + Chr$(Mass(n)) Next MsgBox Zam End Sub

Option

Or mid filtering:

Sub nn() Dim n As Long, TXT As String, L As String, Zam As String TXT = "=""DMITRY KOROTEEV" For n = 1 To Len(TXT) L = Mid$(TXT, n, 1) If L<>"""" And L<>"=" Then Zam = Zam + L Next MsgBox Zam End Sub

Line 6 can be replaced with a like:
Visual Basic code
1
If L Like "[!""=]" Then Zam = Zam + L

Option

Still through position search and recursion:

Sub test() Dim n As Long, txt As String txt = "=""DMITRY KOROTEEV" txt = Change(txt, "=") txt = Change(txt, """") MsgBox txt End Sub Function Change( txt As String, What As String, Optional Pos = 1) Dim n As Long If Pos<>0 Then n = InStr(Pos, txt, What) Change = Mid$(txt, Pos, IIf(n - Pos< 0, Len(txt), n - Pos)) + Change(txt, What, IIf(n = 0, 0, n + 1)) End If End Function

You can also use regular expressions, but I don't know them.

Option

Through right-to-left search and recursion:

Option

And there is Split And Join

Strb = Join(Split(Join(Split(strW, "="), ""), """"), "")

Off-topic: But this is for sadists

Upper and lower case

or StrConv() - convert a string (to and from Unicode, to upper and lower case, capitalize the first letter of words, etc.) - see below

String Operations

For data of type String, there is only one operation - concatenation (union). For example, the result of the concatenation operation of the three string values ​​"Peter" & "" & "Ivanovich" will be the string "Peter Ivanovich". It is also possible to use another operator for the concatenation operation, for example: "ten" + "thousandth". The difference between these expressions is that in the first case, the operands can be values ​​of any type (they will simply be converted to strings), and in the second, both operands must be of type String. There are a large number of functions for working with strings (table. Functions for working with strings).

Table "Functions for working with strings"

To compare string values, you can use the usual numeric comparison operators, since when comparing characters, their binary codes are compared. To compare string values, the Like operator is also used, which allows you to detect an inexact match, for example, the expression "Input signal" Like "Input *" will be True, since the compared string begins with the word "Input". The asterisk character (*) in a string replaces an arbitrary number of characters. Other characters that are processed by the Like operator in the compared string:

• ? – any character (one);
• # - one digit (0-9);
• [<список>] is a character that matches one of the characters in the list;
• [!<список>] is a character that does not match any of the characters in the list.

• ASC() - this function allows you to return the numeric code for the transmitted character. For example, ASC("D") will return 68. This function is useful for determining the next or previous letter. It is usually used in conjunction with the function Chr(), which performs the reverse operation - returns a character according to its numeric code. Variants of this function - AscB() And AscW():
  • AscB() - returns only the first byte of the numeric code for the character.
  • AscW() - returns the code for a Unicode character
• Chr() - returns a character by its numeric code. Can be used in conjunction with the Asc() function, but most often it is used when you need to output a service character (e.g. quotes - "), because quotes just don't enter in VBA code (you need to put double). I usually use this feature.

  Dim sWord As String sWord = Chr(34) & "Quoted Word" & Chr(34)

  There are variants of this function - ChrB() And ChrW(). They work similarly to the same options for the function Asc().

• InStr() And InStrRev() is one of the most popular features. Allows you to find a character or sequence of characters in the body of a string variable and return their position. If the sequence is not found, then 0 is returned.

  Dim sStr As String sStr = "w" If InStr(1, "Hello, World!", sStr, vbTextCompare) > 0 Then MsgBox "Search word exists!" Else MsgBox "The search word is missing!" End If

  The difference between the functions is that InStr() searches for the specified word from the beginning of the string, and InStrRev() from the end of the string

• Left() , Right() , Mid() - the ability to take the number of characters you specify from an existing string variable on the left, right or middle, respectively.

  Dim sStr As String sStr = "Hello, World!" MsgBoxMid(sStr, 1, 5)

• Len() - the ability to get the number of characters in a string. Often used with loops, replace operations, and the like.
• LCase() And UCase() - convert the string to lower and upper cases, respectively. Often used to prepare a value for comparison when the comparison is case insensitive (surnames, names of firms, cities, etc.).
• LSet() And RSet() - the ability to fill one variable with the symbols of another without changing its length (left and right respectively). Extra characters are cut off, spaces are substituted for the missing ones.
• LTrim() , RTrim() , trim() - the ability to remove spaces, respectively, on the left, right, or both left and right.
• Replace() - the ability to replace one sequence of characters in a string with another.

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello", "Bay")

• Space() - get a string from the number of spaces you specified;
  Another similar feature is Spc() , which is used to format console output. It multiplies spaces based on the width of the command line.
• StrComp() - Ability to compare two strings.
• StrConv() - the ability to convert a string (to and from Unicode, to uppercase and lowercase, to capitalize the first letter of words, etc.):

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!", vbUpperCase)

  Constants can be used as the second parameter of the parameter:

  • vbUpperCase: Converts all text characters to UPPERCASE
  • vbLowerCase: Converts all text characters to lowercase
  • vbProperCase: Converts the first character of each word to uppercase
  • *vbWide: Converts the characters of a string from single-byte to double-byte
  • *vbNarrow: Converts the characters of a string from double-byte to single-byte
  • **vbKatakana: Converts Hiragana characters to Katakana characters
  • **vb Hiragana: Converts Katakana characters to Hiragana characters
  • ***vb Unicode: Converts a string to Unicode using the system's default code page
  • ***vbFromUnicode: Converts a string from Unicode to the system default code page

  * applicable for Far East localizations
  ** applicable to Japan only
  *** not supported on Macintosh operating systems

• StrReverse() - "reverse" the string by placing its characters in reverse order. The function works only starting from Excel 2000 and higher. An example of using the function, as well as other methods for flipping a word, can be found in this article: How to flip a word?
• Tab() is another function that is used to format console output. Multiplies tab characters as many as you specify. If no number is specified, simply inserts a tab character. You can also use the constant to insert a tab character into a string value vbTab.
• String() - allows you to get a string from the specified number of characters (which again are specified by you). Typically used to format output in conjunction with the function Len().

The following three functions allow you to work with an array of strings

Split (<строка> [, <разделитель>]) - Converts a string to an array of substrings. The default separator is a space. This function is useful for splitting sentences into words. However, you can specify any other delimiter in this function. For example, Split(3, "This is a test offer") returns an array of three string values: "This", "test", "offer". Join(<массивСтрок> [, <разделитель>]) - converts an array of strings into a single string with the specified delimiter. Filter(<массивСтрок>, <строкаПоиск>[, <включение>] [, <сравнение>]) - iterates through an array of string values ​​and looks for all substrings in it that match the given string. This function has four arguments:<строкаПоиск>- search string;<включение>– a parameter (Boolean value) that indicates whether the returned strings will include the searched substring or, conversely, only those array strings that do not contain the searched string as a substring will be returned;<сравнение>– a parameter that defines the string comparison method. Three more functions provide string conversion: LCase(<строка>) - converts all characters of the string to lowercase, for example, the LCase("MAIL") function returns the string "mail"; UCase(<строка>) - converts all characters of the string to upper case; StrConv(<строка>, <преобразование>) - performs several types of string conversions depending on the second parameter. This parameter is described by built-in constants, for example, the StrConv("Poccia", VbProperCase) function returns the value "Russia".

And the last two functions generate character strings

Space(<число>) - creates a string consisting of the specified number of spaces; String(<число>, <символ>) - creates a string consisting of the number of characters specified in the first argument. The symbol itself is specified in the second argument.

Example

Write a program that works with string variables. To do this, create a form whose labels contain the following messages: 1 label: the length of the string entered in the first text field (1 line) is reported; 2 label: converts all characters of the third text field (line 3) to capital letters; 3 label: prints together the contents of the first and second text fields (lines 1 and 2).

Execution technology

• Open the Word application, save the document, and switch to the VBA editor.
• Create a form in the same way as shown in the figure.
• Write an event handler for the OK button.
• Compile the program.
• Run the form for execution.

Private Sub CommandButton1_Click() Dim a As String Dim b As String Dim c As String Dim k As String Dim d As String Dim n As Integer a=TextBox1.Text n=Len(a) Label7.Caption="the length of the first line is" & n & "characters" c=TextBox3.Text k=Ucase(c) Label8.Caption=k b=TextBox2.Text d=a + » » + b Label9.Caption=d End Sub

Task

It is necessary that Excel in cell A1 searches for words written comma-separated in column A2, and the result, and the words found, with the number of their repetitions (if possible) in the searched text, are written to another, third, cell. (it would be even better if they were highlighted (well, or underlined) in some way in the first cell ... so that they would be immediately visible. This is how you drive words into an array:

And then in the cycle for all the words you are looking for (again in the cycle. in the nested) Added after 23 minutes

Added after 15 minutes And this is how it will search at the beginning of the word and select the entire word

You can do what would be looking for and not from the beginning of the word, but by part of the word. - add another Do-Loop. Only shift the beginning (ST) to the left until a space (http://www.cyberforum.ru/vba/thread567588.html)

How to: Match a String against a Pattern (Visual Basic)

Compliance check string to template String Data Type (Visual Basic) - Like operator (Visual Basic) . the left operand of the Like operator is a string expression, and the right operand is a template string Like returns a value of type Boolean

Symbol check

? - any one character

• myString consists of a W character followed by any 2 characters

  Dim sMatch As Boolean = myString Like "W??"

Any character from the list and range

Any character and then one of the characters A, C, or E

Dim sMatch As Boolean = myString Like "?" case sensitive

• myString = num characters and then one character from the range: i, j, k, l, m, or n:

  Dim sMatch As Boolean = myString Like "num" case sensitive

Like works with as with a zero-length string array string (""). lets you check if a string is empty

Character from the list or the absence of a character

1. The Like operator is used twice and the results are combined using the Or Operator (Visual Basic) or OrElse Operator (Visual Basic) .
2. In the first statement template, insert a list of characters in square brackets ().
3. In the second statement template, don't put anything in the place of the test. Example: Testing the seven-digit phone number phoneNum, which must contain exactly three digits, followed by a space, a hyphen, a period, or no character, and then four digits. (the first three digits may not be separated from the last four - "no character") Null , an error occurs. If an argument is given comparison_type , argument start_position is mandatory. line_1 Required. string expression The on which to search. line_2 Required. The desired string expression. comparison_type Optional. Specifies the type string comparisons. If the value of the argument comparison_type is Null, an error occurs. If the argument comparison_type omitted, type of comparison defined by parameter Meaning Wedavenue. Specify a valid LCID (LocaleID) parameter to use the comparison rules specified in the language settings.

   Options

   Argument comparison_type uses the following options:

   Return Values

   Remarks

   Function InStrB used with byte data contained in a string. Function InStrB returns the byte position, not the character position, of the first occurrence of one string within another.

   Examples

   Using the InStr function in an expression Every time you use expressions, you can use the function InStr. For example, if you want to determine the position of the first point ( . ) in the field that contains the IP address (named "IPAddress"), you can use the function InStr to search for it:

   InStr(1,,".")

   Function InStr looks at each value in the "IPAddress" field and returns the position of the first point. Therefore, if the value of the first octet of the IP address is 10. , the function returns 3.

   You can use other functions that use the result of the function InStr, to extract the value of the IP address octet that precedes the first dot, for example:

   Left(,(InStr(1,."")-1))

   In this example, the function InStr(1,,".") returns the position of the first point. The result of subtracting 1 is the number of characters preceding the first period, in this case - 2. Then the function Left extracts those characters from the left side of the "IPAddress" field, returning a value of 10.

   Function use InStr in Visual Basic for Applications (VBA) code

   NOTE. The following examples illustrate how to use the capabilities of a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer's guide in the dropdown next to the button Search, and then enter one or more keywords in the search box.

   In this example, the function InStr is used to get the position of the first occurrence of one string within another.

   Dim SearchString, SearchChar, MyPos SearchString ="XXpXXpXXPXXP" " String to search in. SearchChar = "P" " Search for "P". " A textual comparison starting at position 4. Returns 6. MyPos = Instr( 4, SearchString , searchchar , 1) " A binary comparison starting at position 1. Returns 9. MyPos = Instr( 1, SearchString , searchchar , 0) " Comparison is binary by default " (last argument is omitted). MyPos= Instr( SearchString , searchchar ) Returns 9. MyPos = Instr( 1, SearchString , "W" ) Returns 0.

So, we continue our Pascal lessons for beginners. In the last lesson, we discussed , but there we mentioned characters, so before we deeply study the data type, we will learn about the Char type. Character data type Char is a data type whose values ​​are single characters. This type can contain only one any character (For example: "*", "/", ".", "!" and others). Each such character occupies 8 bits of memory, in total there are 256 eight-bit characters. All characters used by the character type Char are written in the ASCII (American Standard Code for Information Interchange) character table, or American Standard Code for Information Interchange.

Character constants are enclosed in apostrophes, such as ".", "*", "7", "s". Also, a character constant can be written using a symbol - "lattice", for example # 185 - will display the character at number 185 from the ASCII table (this is the "#" character).

There are 5 functions applicable to the character type: Ord, Chr, Pred, Succ and Upcase.

The Ord function converts a character to its numeric code from ASCII tables. For example, for the character "#" it will return the value 185. The Chr function is the inverse of the Ord function. The Chr function converts the numeric character code to the character itself, for example, if you take the numeric code 64, then the Chr (64) function will return the "@" character.

An example of a Pascal program using the Ord function:

Begin //Program start

writeln(ord(x)); //Display number in ASCII table
end. // End of program

An example of a Pascal program using the Chr function:

varx:integer; // Description of variables (x - integer type)
Begin //Program start
readln(x); //Reading a variable
writeln(chr(x)); //Display character by number in ASCII table
end. // End of program

The Pred function returns the value of the previous character from the ASCII table, for example, for the character "P" (Pred(P)) this function will return the character "O". The Succ function is the inverse of the Pred function. For the character "P", the Succ(P) function will return the character "Q", which is the next character from the above ASCII table.

An example of a Pascal program using the Pred and Succ functions:

varx:char; // Description of variables (x - character type)
Begin //Program start
readln(x); //Reading a variable
writeln(pred(x)); //Output the previous character in the ASCII table
writeln(succ(x)); //Output the next character in the ASCII table
end. // End of program

The UpCase function is only applicable to lowercase English letters. This function converts lowercase English letters to uppercase.

An example of a Pascal program using the UpCase function:

Appendix to the lesson - ASCII character tables:

The lesson is over for today. remember, that pascal programming simple and is the basis for many programming languages.

• ASC () - this function allows you to return the numeric code for the transmitted character. For example, ASC("D") will return 68. This function is useful for determining the next or previous letter. It is usually used in conjunction with the function Chr(), which performs the reverse operation - returns a character according to its numeric code. Variants of this function - AscB() And AscW():
  • AscB () - returns only the first byte of the numeric code for the character.
  • AscW () - returns the code for a Unicode character
• Chr () - returns a character by its numeric code. Can be used in conjunction with the Asc() function, but most often it is used when you need to output a service character (e.g. quotes - "), because quotes just don't enter in VBA code (you need to put double). I usually use this feature.

  Dim sWord As String sWord = Chr(34) & "Quoted Word" & Chr(34)

  There are variants of this function - ChrB() And ChrW(). They work similarly to the same options for the function Asc().

• InStr () And InStrRev () is one of the most popular features. Allows you to find a character or sequence of characters in the body of a string variable and return their position. If the sequence is not found, then 0 is returned.

  Dim sStr As String sStr = "w" If InStr(1, "Hello, World!", sStr, vbTextCompare) > 0 Then MsgBox "Search word exists!" Else MsgBox "The search word is missing!" End If

  The difference between the functions is that InStr() searches for the specified word from the beginning of the string, and InStrRev() from the end of the string

• Left () , Right () , Mid () - the ability to take the number of characters you specify from an existing string variable on the left, right or middle, respectively.

  Dim sStr As String sStr = "Hello, World!" MsgBoxMid(sStr, 1, 5)

  Dim sStr As String sStr = "Hello, World!" MsgBoxMid(sStr, 1, 5)

• Len () - the ability to get the number of characters in a string. Often used with loops, replace operations, and the like.
• LCase () And UCase () - convert the string to lower and upper cases, respectively. Often used to prepare a value for comparison when the comparison is case insensitive (surnames, names of firms, cities, etc.).
• LSet () And RSet () - the ability to fill one variable with the symbols of another without changing its length (left and right respectively). Extra characters are cut off, spaces are substituted for the missing ones.
• LTrim () , RTrim () , trim () - the ability to remove spaces, respectively, on the left, right, or both left and right.
• Replace () - the ability to replace one sequence of characters in a string with another.

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello" , "Bay" )

  Dim sStr As String sStr = "Hello, World!" MsgBox Replace(sStr, "Hello", "Bay")

• Space () - get a string from the number of spaces you specified;
  Another similar feature is Spc () , which is used to format console output. It multiplies spaces based on the width of the command line.
• StrComp () - Ability to compare two strings.
• StrConv () - the ability to convert a string (to and from Unicode, to uppercase and lowercase, to capitalize the first letter of words, etc.):

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!" , vbUpperCase)

  Dim sStr As String sStr = "Hello, World!" MsgBox StrConv("Hello, World!", vbUpperCase)

  Constants can be used as the second parameter of the parameter:

  • vbUpperCase: Converts all text characters to UPPERCASE
  • vbLowerCase: Converts all text characters to lowercase
  • vbProperCase: Converts the first character of each word to uppercase
  • *vbWide: Converts the characters of a string from single-byte to double-byte
  • *vbNarrow: Converts the characters of a string from double-byte to single-byte
  • **vbKatakana: Converts Hiragana characters to Katakana characters
  • **vb Hiragana: Converts Katakana characters to Hiragana characters
  • ***vb Unicode: Converts a string to Unicode using the system's default code page
  • ***vbFromUnicode: Converts a string from Unicode to the system default code page

  * applicable for localization of the Far East
  ** applicable to Japan only
  *** not supported on Macintosh operating systems

• StrReverse () - "reverse" the string by placing its characters in reverse order. The function works only starting from Excel 2000 and higher. An example of using the function, as well as other methods for flipping a word, can be found in this article: How to flip a word?
• Tab () is another function that is used to format console output. Multiplies tab characters as many as you specify. If no number is specified, simply inserts a tab character. You can also use the constant to insert a tab character into a string value vbTab.
• String () - allows you to get a string from the specified number of characters (which again are specified by you). Typically used to format output in conjunction with the function Len().

Chr function

Function Chr(Ch aracte r) allows you to get a character by the value of its ANSI or Unicode numeric code

Functions Chr, ChrB, ChrW return a String subtype value of type Variant containing the character corresponding to the specified ANSI or Unicode character code. Functions Chr$, ChrB$, ChrW$ return respectively a value of type String

Chr And Chr$ return a character by its ANSI encoding
ChrB And ChrB$ return one-byte string
ChrW returns a Unicode character, but on non-Unicode systems it behaves the same Chr
Use in parameter CharCode values ​​greater than 255 generate runtime errors 5: Invalid procedure call or argument or 6: Overflow