Top-office11.ru

IT и мир ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel substring

VBA Substring – How to Substring in Excel Macro

Substring is one of the most popular function in any programming language. It eases your tasks while dealing with strings. As the name suggests a substring function divides a string into different parts based on a particular criteria.

There are multiple VBA Substring functions. In practical situations these substring functions can be quite useful in extracting a portion of a string.

Today in this post I am going to explain all the VBA substring functions that you can use in Excel macros:

1. LEFT Substring function:

The LEFT function in Excel VBA is used for fetching a specified numbers of characters from the start of the string. The syntax of LEFT function is as follows:

Left (text_string, length)

  • Here ‘text_string’ refers to input string which is to be separated.
  • And ‘length’ refers to the number to characters to be extracted.
  • Left («Exceltrick», 5) gives an output “Excel”
  • Left («SomeText», 4) gives the result “Some”

Note: Instead of using a hardcoded string in the first argument you can also fetch ‘text_string’ from your excel sheet like: ActiveSheet.Range(“A1”).

2. Right Substring function:

The RIGHT Function in Excel VBA is just opposite to the LEFT function. It returns a specified number of characters from the end of text string. The syntax of RIGHT function is as follows:

Right (text_string, length)

  • Here ‘text_string’ refers to input string which is to be separated.
  • And ‘length’ refers to the number to characters to be extracted but extraction begins from the right side.
  • Right («Exceltrick», 5) gives an output “trick”
  • Right («SomeText», 4) gives the result “Text”

Note: Instead of using a hardcoded string in the first argument you can also fetch ‘text_string’ from your excel sheet as ActiveSheet.Range(“A1”).

3. MID Substring function:

MID is a much better function than the first two, it gives you the ability to specify the start and end positions of the extracted string. The syntax of MID VBA Substring function is as under:

Mid(text_string, start_position, Length)

  • Here ‘text_string’ refers to input string which is to be separated.
  • ‘start_position’ refers to the numeric position from where extraction is to be started.
  • And ‘length’ refers to the number to characters to be extracted.
  • MID («Exceltrick», 2,4) gives an output “celt”
  • MID («SomeText», 4,4) gives the result “Text”

Note: Instead of using a hardcoded string in the first argument you can also fetch ‘text_string’ from your excel sheet as ActiveSheet.Range(“A1”).

4. SPLIT Substring function:

SPLIT function is another VBA function that can be used for sub-stringing or splitting a string. SPLIT function can come very handy when you are dividing a text string into more than one parts based on a delimiter. The syntax of a split function is as under:

Split (text_string, Delimiter, limit, Compare)

  • Here, ‘text_string’ refers to input string which is to be separated.
  • ‘Delimiter’ refers to the delimiter character which separates the string into parts. This is an optional argument, if it is left blank then, space character ” ” is assumed to be the default delimiter.
  • ‘limit’ refers to the maximum number of substring parts into which the string should be divided. It is also an optional argument, the default value is (-1) which means that substring should happen at every position where delimiter is encountered.
  • ‘compare’ it is an optional numerical value which specifies the comparison to use when evaluating substrings.

For instance you have a text string as “This is a text string” and now you have to break this string into individual words, so in this case you will use space ” ” as a delimiter. The split function will be used as:

Читать еще:  Vba excel вставка строки

Split («This is a text string», » «)

The result of this split function is an array of words: “This” “is” “a” “text” “string”.

VBA Substring Macro Example:

Below I have created a macro that illustrates all the substring techniques available in VBA programming. This is a simple and self-explanatory macro, in this I have simply divided a text string with the 4 methods that I have described above.

Below is the code that I have used for this macro:

So, this was all about VBA substring functions. Do share your view related to the topic.

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into ‘Excel Geeks’.

Vba excel substring

The result delivered is:

The Mid Function has extracted the four letters of AutomateExcel starting from the second character/position/letter which are utom.

Finding the Position of a Substring

The VBA Instr String Function

The VBA Instr Function returns the starting position of a substring within another string. This function is case-sensitive. The syntax of the VBA Instr String Function is:

Instr([Start], String, Substring, [Compare]) where:

  • Start (Optional) – This specifies the starting position for the function to search from. If blank, the default value of 1 is used.
  • String – The original text.
  • Substring The substring within the original text that you want to find the position of.
  • Compare (Optional) – This specifies the type of comparison to make. If blank, binary comparison is used.

-vbBinaryCompare – Binary comparison (Upper and lower case are regarded as different)
-vbTextCompare – Text comparison (Upper and lower case are regarded as the same)
-vbDatabaseCompare – Database comparison (This option is used in Microsoft Access only, and is a comparison based on the database)

The following code shows you how to use the Instr String Function to determine the first occurrence of the substring “Th” within the main string:

The result delivered is:

The Instr Function has returned the position of the first occurrence of the substring “Th” which is 1. Note this function includes the spaces in the count.

The VBA InstrRev String Function

The VBA InstrRev Function returns the starting position of a substring within another string but it starts counting the position, from the end of the string. This function is case-sensitive. The syntax of the VBA InstrRev String Function is:

InstrRev(String, Substring, [Start], [Compare]) where:

  • String – The original text.
  • Substring The substring within the original text that you want to find the position of.
  • Start (Optional) – This specifies the position to start searching from. If blank, the function starts searching from the last character.
  • Compare (Optional) – This specifies the type of comparison to make. If blank, binary comparison is used.

-vbBinaryCompare – Binary comparison (Upper and lower case are regarded as different)
-vbTextCompare – Text comparison (Upper and lower case are regarded as the same)
-vbDatabaseCompare – Database comparison (This option is used in Microsoft Access only, and is a comparison based on the database)

The following code shows you how to use the InstrRev String Function to determine the first occurrence of the substring “Th” within the main string, starting from the end of the string:

Vba excel substring

Для данных типа String существует только одна операция — конкатенация (объединение). Например, результатом операции конкатенации трех строковых значений «Петр» & » » & «Иванович» будет строка «Петр Иванович». Возможно также использование другого оператора для операции конкатенации, например: «десяти» + «тысячник». Разница между этими выражениями состоит в том, что в первом случае операндами могут быть значения любого типа (они просто будут преобразовываться в строковые), а во втором — оба операнда должны иметь тип String.

Читать еще:  Executeexcel4macro vba описание

Для работы со строками существует большое количество функций (таблица. Функции работы со строками).

Таблица «Функции работы со строками»

ФункцияОписаниеПример
Len(str)Определяет длину строкиИз а=lеn(«Персонажи») следует а=9
Left ( , )Выделяет из аргумента указанное количество символов слеваLeft(» 1234string», 4) =»1234″
Right( , )Выделяет из аргумента указанное количество символов справаRight(» 1234string», 6) =»string»
Mid( , [, ])Выделяет из аргумента подстроку с указанным числом символов, начиная с позицииM
Mid( , )Выделяется подстрока от позиции до конца строкиM
LTrim ( )Удаляет пробелы в начале строкиLTrim(» печать») =»печать»
RTrim ( )Удаляет пробелы в конце строкиRTrim(«печать «) =»печать»
Trim ( )Удаляет пробелы в начале и в конце строкиTrim(» печать «) =»печать»
InStr([ , ] , [, ])Производит поиск подстроки в строке. Возвращает позицию первого вхождения строки в строку , — позиция, с которой начинается поиск. Если этот аргумент пропущен, поиск начинается с начала строкиInstr(«C:Temp test.mdb», «Test»)=9 Если искомая строка не находится в указанной строке, функция возвращает 0
InStrRev ([ , ] , [, ])Ищет подстроку в строке, но начинает поиск с конца строки и возвращает позицию последнего вхождения подстроки. Необязательный аргумент определяет тип сравнения двух строк
Replace ( , , )Позволяет заменить в строке одну подстроку другой. Эта функция ищет все вхождения аргумента в аргументе и заменяет их на

Для сравнения строковых значений можно использовать обычные операторы сравнения числовых значений, так как при сравнении символов сравниваются их двоичные коды.

Для сравнения строковых значений также применяется оператор Like, который позволяет обнаруживать неточное совпадение, например выражение «Входной сигнал» Like «Вход*» будет иметь значение True, так как сравниваемая строка начинается со слова «Вход». Символ звездочка (*) в строке заменяет произвольное число символов. Другие символы, которые обрабатываются оператором Like в сравниваемой строке:

  • ? — любой символ (один);
  • #- одна цифра (0-9);
  • [ ] — символ, совпадающий с одним из символов списка;
  • [! ] — символ, не совпадающий ни с одним из символов списка.

Следующие три функции позволяют работать с массивом строк

  • Split ( [, ]) — преобразует строку в массив подстрок. По умолчанию в качестве разделителя используется пробел. Данную функцию удобно использовать для разбиения предложения на слова. Однако можно указать в этой функции любой другой разделитель. Например, Split(3, «Это тестовое предложение») возвращает массив из трех строковых значений: «Это», «тестовое», «предложение».
  • Join ( [, ]) — преобразует массив строк в одну строку с указанным разделителем.
  • Filter ( , [, ] [, ]) — просматривает массив строковых значений и ищет в нем все подстроки, совпадающие с заданной строкой.

Эта функция имеет четыре аргумента:

  • — искомая строка;
  • — параметр (boolean значение), который указывает, будут ли возвращаемые строки включать искомую подстроку или, наоборот, возвращаться будут только те строки массива, которые не содержат искомой строки в качестве подстроки;
  • — параметр, определяющий метод сравнения строк.

Еще три функции обеспечивают преобразование строк:

  • LCase ( ) — преобразует все символы строки к нижнему регистру, например функция LCase(«ПОЧTA») возвращает строку «почта»;
  • UCase ( ) — преобразует все символы строки к верхнему регистру;
  • StrConv ( , ) — выполняет несколько типов преобразований строки в зависимости от второго параметра. Этот параметр описывается встроенными константами, например функция StrConv(«poccия», VbProperCase) возвращает значение «Россия».

И последние две функции генерируют строки символов

  • Space ( ) — создает строку, состоящую из указанного числа пробелов;
  • String ( , ) — создает строку, состоящую из указанного в первом аргументе числа символов. Сам символ указывается во втором аргументе.

Пример

Создать программу, работающую со строковыми переменными. Для этого создать форму, в метки которой выходят следующие сообщения:

1 метка: сообщается длина строки, введенной в первое текстовое поле (1 строка);

Читать еще:  Как поставить день недели в excel

2 метка: преобразует все символы третьего текстового поля (3 строка) в заглавные буквы;

3 метка: выводит вместе содержание первого и второго текстовых полей (1 и 2 строки).

Хороший фреймворк (framework) лучшее решение для безпроблемной работы со строками.

Технология выполнения

  • Откройте приложение Word, сохраните документ и перейдите в редактор VBA.
  • Создайте форму аналогично приведенному рисунку.
  • Пропишите обработчик события кнопки ОК.
  • Откомпилируйте программу.
  • Запустите форму на выполнение.

Форма примера в режиме конструктора и в рабочем состоянии

Функции VBA для работы с текстом

  • ASC ()— эта функция позволяет вернуть числовой код для переданного символа. Например, ASC( «D» ) вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду.Варианты этой функции — AscB() и AscW():
    • AscB () — возвращает только первый байт числового кода для символа.
    • AscW () — возвращает код для символа в кодировке Unicode
  • Chr () — возвращает символ по его числовому коду. Может использоваться в паре с функцией Asc(), но чаще всего её применяют, когда нужно вывести служебный символ (например кавычки — » ), т.к. кавычки просто так в VBA-коде не ввести(нужно ставить двойные). Я обычно именно эту функцию и использую.

    Dim sWord As String sWord = Chr(34) & «Слово в кавычках» & Chr(34)

    Есть варианты этой функции — ChrB() и ChrW(). Работают аналогично таким же вариантам для функции Asc().
    InStr () и InStrRev ()— одна из самых популярных функций. Позволяет обнаружить в теле строковой переменной символ или последовательность символов и вернуть их позицию. Если последовательность не обнаружена, то возвращается 0.

    Dim sStr As String sStr = «w» If InStr(1, «Hello, World!», sStr, vbTextCompare) > 0 Then MsgBox «Искомое слово присутствует!» Else MsgBox «Искомое слово отсутствует!» End If

    Разница функций в том, что InStr() ищет указанное слово от начала строки, а InStrRev() с конца строки
    Left (), Right (), Mid ()— возможность взять указанное вами количество символов из существующей строковой переменной слева, справа или из середины соответственно.

    Dim sStr As String sStr = «Hello, World!» MsgBox Mid(sStr, 1, 5)

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

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

    В качестве второго параметра параметра могут применяться константы:

    • vbUpperCase: Преобразует все текстовые символы в ВЕРХНИЙ РЕГИСТР
    • vbLowerCase: Преобразует все текстовые символы в нижний регистр
    • vbProperCase: Переводит первый символ каждого слова в Верхний Регистр
    • *vbWide: Преобразует символы строки из однобайтовых в двухбайтовые
    • *vbNarrow: Преобразует символы строки из двухбайтовых в однобайтовые
    • **vbKatakana: Преобразует символы Hiragana в символы Katakana
    • **vbHiragana: Преобразует символы Katakana в символы Hiragana
    • ***vbUnicode: Преобразует строку в Юникод с помощью кодовой страницы системы по умолчанию
    • ***vbFromUnicode: Преобразует строку из Юникод в кодовую страницу системы по умолчанию

    * применимо для локализацией Дальнего востока
    ** применимо только для Японии
    *** не поддерживается операционными системами под управлением Macintosh

  • StrReverse () — «перевернуть» строку, разместив ее символы в обратном порядке. Функция работает только начиная от Excel 2000 и выше. Пример использования функции, а так же иные методы переворачивания слова можно посмотреть в этой статье: Как перевернуть слово?
  • Tab () — еще одна функция, которая используется для форматирования вывода на консоль. Размножает символы табуляции в том количестве, в котором вы укажете. Если никакое количество не указано, просто вставляет символ табуляции. Для вставки символа табуляции в строковое значение можно также использовать константу vbTab.
  • String () — позволяет получить строку из указанного количества символов (которые опять-таки указываются Вами). Обычно используются для форматирования вывода совместно с функцией Len().
  • Ссылка на основную публикацию
    Adblock
    detector