Строковые функции Excel VBA: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV

Строковые функции Excel VBA:

Строковые функции Excel VBA для поиска и замены текста с примерами: LEFT, RIGHT, MID, LEN, REPLACE, Функции InStr и InStrRev

———————————— ——————-

Содержание:

Функция LEFT (рабочий лист/VBA)

Функция RIGHT (рабочий лист/VBA)

Функция MID (рабочий лист/VBA)

Функция LEN (рабочий лист/VBA)

Функция REPLACE (рабочий лист)

Функция REPLACE (VBA)

InStr & Функции InStrRev (VBA)

——————————- ————————

В Excel vba String относится к последовательности смежных символов в кавычках, а именно. «Это строковое выражение в кавычках в vba». Эти символы буквально интерпретируются как символы в том смысле, что они представляют сами символы, а не их числовые значения. Строка может включать буквы, цифры, пробелы и знаки препинания. Строковое выражение может иметь в качестве своих элементов строку из смежных символов, функцию, возвращающую строку, строковую переменную, строковую константу или вариант строки. В этом разделе подробно обсуждается использование строковых функций Excel VBA для управления текстовыми строками с помощью кода vba. Также см. Связанную ссылку: Строковые функции Excel VBA: SPLIT, JOIN, CONCATENATE .

Функция LEFT (рабочий лист/VBA)

Функция Excel LEFT может использоваться как как функция рабочего листа, так и как Функция VBA. Функция LEFT возвращает указанное количество символов в текстовой строке, начиная с первого или крайнего левого символа. Используйте эту функцию для извлечения подстроки из левой части текстовой строки. Синтаксис: LEFT (текстовая_строка, символьные_числа) . Необходимо упомянуть аргумент text_string , который представляет собой текстовую строку, из которой вы хотите извлечь указанное количество символов. Аргумент char_numbers является необязательным (при использовании в качестве функции рабочего листа), он указывает количество символов, извлекаемых из текстовой строки. Значение char_numbers должно быть равно или больше нуля; если она больше, чем длина текстовой строки, функция LEFT вернет текстовую строку полностью; если опущено, по умолчанию будет 1. При использовании в качестве функции VBA необходимо указать оба аргумента, и если text_string содержит Null, функция также возвращает Null.

Функция ПРАВО (рабочий лист/VBA)

Функция Excel RIGHT может использоваться как функция рабочего листа, так и функция VBA. Функция RIGHT возвращает указанное количество символов в текстовой строке, начиная с последнего или самого правого символа. Используйте эту функцию для извлечения подстроки из правой части текстовой строки. Синтаксис: RIGHT (текст_строка, символьные_числа) . Необходимо упомянуть аргумент text_string , который представляет собой текстовую строку, из которой вы хотите извлечь указанное количество символов. Аргумент char_numbers является необязательным (при использовании в качестве функции рабочего листа), он указывает количество символов, извлекаемых из текстовой строки. Значение char_numbers должно быть равно или больше нуля; если она больше, чем длина текстовой строки, функция RIGHT вернет текстовую строку полностью; если опущено, по умолчанию будет равно 1. При использовании в качестве функции VBA необходимо указать оба аргумента, и если text_string содержит Null, функция также возвращает Null.

Функция MID (рабочий лист/VBA)

Функция Excel MID может использоваться как функция рабочего листа, так и функция VBA. Функция MID возвращает указанное количество символов в текстовой строке, начиная с указанной позиции (т. Е. Начиная с указанного номера символа). Используйте эту функцию для извлечения подстроки из любой части текстовой строки. Синтаксис: MID (текстовая_строка, начальное_число, символьные_числа) . Аргумент text_string — это текстовая строка, из которой вы хотите извлечь указанное количество символов. Аргумент start_number указывает номер символа, с которого следует начать извлечение подстроки, при этом первым символом в текстовой строке является start_number 1 и увеличивается вправо. Аргумент char_numbers указывает количество символов, извлекаемых из текстовой строки.

Если start_number больше, чем длина текстовой строки, возвращается пустая строка (нулевой длины); если он меньше, чем длина текстовой строки, но вместе с char_numbers (т.е. start_number PLUS char_numbers) он больше, чем длина текстовой строки, функция MID вернет текстовую строку полностью от позиции start_number до конца текстовой строки.

Использование функции MID как функции рабочего листа , если для char_numbers указано отрицательное значение, MID вернет #VALUE! значение ошибки; если start_number меньше 1, MID вернет #VALUE! значение ошибки. Все аргументы необходимо указать при использовании функции рабочего листа.

Использование функции MID в качестве функции VBA: Аргумент char_numbers является необязательным при использовании в качестве функции VBA, а если он опущен, функция вернет текстовую строку полностью от позиции start_number до конца текстовой строки. Все остальные аргументы необходимо указать при использовании в качестве функции vba. Если text_string содержит Null, функция также возвращает Null.

Функция LEN (рабочий лист/VBA)

Функция LEN в Excel может использоваться как как функция рабочего листа, так и как функция VBA. Функция LEN рабочего листа возвращает количество символов в текстовой строке. Используйте эту функцию, чтобы получить длину текстовой строки. Синтаксис: LEN (текстовая_строка) . Необходимо упомянуть аргумент text_string , который представляет собой текстовую строку, длину которой вы хотите получить в количестве символов. Обратите внимание, что пробелы также считаются символами. Функция LENB рабочего листа возвращает количество байтов, используемых для представления символов в текстовой строке — каждый символ считается 1 байтом, кроме случаев, когда язык DBCS [а именно. Японский, китайский (упрощенный), китайский (традиционный) и корейский] установлен в качестве языка по умолчанию, при этом символ считается как 2 байта. Синтаксис: LENB (текст_строка) .

Пока с использованием LEN как функция VBA — Синтаксис: Len (text_string) или Len (variable_name) — вы можете использовать текстовую строку или имя переменной, и функция вернет значение типа Long, представляющее количество символов, содержащихся в строке, или количество байтов, необходимых для хранения переменной. Использование функции vba Len для переменной типа вариант будет рассматривать переменную как строку и возвращать количество содержащихся в ней символов. Text_string или переменная, содержащая Null, также вернет Null. Функция vba Len возвращает количество символов в строке, где переменная имеет подтип String или Variant, а переменная имеет подтип numeric, функция возвращает количество байтов, используемых для хранения переменной.

Пример — использование функций Left, Right, Mid и Len в коде vba.

Sub Left_Right_Mid_Len ()
‘с использованием функций vba Left, Right, Mid & Len.

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

str = «Джеймс Бонд»

strLeft = Left (str, 7)

‘возвращает «Джеймс Б», то есть первые 7 символов (пробел считается отдельным символом).

MsgBox strLeft

strLeft = Left (str, 15)

‘возвращает «Джеймс Бонд», все символы в ячейке A1, потому что число 15, указанное в функции, превышает длину строки в 10 символов..

MsgBox strLeft

strRight = Right (str, 7)

‘возвращает «es Bond «, которые представляют собой последние 7 символов (пробел считается отдельным символом).

MsgBox strRight

strRight = Right (str, 15)

‘возвращает «Джеймс Бонд», все символы в ячейке A1, потому что число 15, указанное в функции, превышает длину строки в 10 символов.

MsgBox strRight

strMid = Mid (str, 2, 6)

‘Возвращает «ames B» . Начинается со второго символа т.е. «a», а затем указывает, что 6 символов должны быть возвращены, начиная с «a».

MsgBox strMid

strMid = Mid (str, 2, 15)

‘Возвращает «Эймс Бонд». Возвращает все символы, начиная со второго символа (позиция start_number) «a», поскольку указанные символы из 15 плюс начальное число 2 (то есть всего 17) превышают длину строки в 10 символов.

MsgBox strMid

strMid = Mid (str, 2)

‘Возвращает «Эймс Бонд». Возвращает все символы, начиная со второго символа (позиция start_number) «a», поскольку второй аргумент (char_numbers) опущен.

MsgBox strMid

strMid = Mid (str, 12, 2)

‘Возвращает пустую строку (нулевой длины), поскольку начальное число 12 превышает длину строки в 10 символов.

MsgBox strMid

‘Возвращает 10 — длину строки, измеряемую количеством символов.

MsgBox Len (str)

‘Возвращает 10 — длину строки, измеряемую ее количеством символов.

MsgBox Len ( «Джеймс Бонд»)

End Sub

Пример — Использование функции vba Len — типы переменных.

Sub Len_vbaFunc ()
‘с использованием функции vba Len — типы переменных

‘—————————

‘ в обоих случаях возвращает 3 — количество символов в строке:

MsgBox Len («bad»)

MsgBox Len («245»)

‘возвращает 10 — количество символов в строке, включая пробел:

MsgBox Len («James Bond»)

‘—————————

‘ переменная варианта типа обрабатывается как строка

Dim vVar As Variant

vVar = 245

‘возвращает 2, указывая переменную subtype Integer:

MsgBox VarType (vVar)

‘Возвращает 3, количество символов, содержащихся в переменной — функции Len обрабатывают вариантную переменную как строку:

MsgBox Len (vVar)

‘—————— ———-

‘переменная типа string

Dim strVar As String

strVar = » Джеймс Бонд «

‘возвращает 8, указывая подтип переменной String:

M sgBox VarType (strVar)

‘Возвращает 10, количество символов, содержащихся в переменной типа String:

MsgBox Len (strVar)

‘—————————

‘ переменная типа integer

Dim iVar As Integer

iVar = 245

‘Возвращает 2, количество байтов, используемых для хранения переменная:

MsgBox Len (iVar)

‘переменная типа long

Dim lVar As Long

lVar = 245

‘Возвращает 4, количество байтов, используемых для хранения переменной:

MsgBox Len (lVar)

‘переменная типа single

Dim sVar As Single

sVar = 245,567

‘Возвращает 4, количество байтов, используемых для хранения переменной:

MsgBox Len (sVar)

‘переменная типа double

Dim dVar As Double

dVar = 245.567

‘ возвращает 8 , количество байтов, используемых для хранения переменной:

MsgBox Len (dVar)

‘————— ————

End Sub

Пример — использование функций LEN и MID для определения символов, появляющихся в позициях нечетных номеров в текстовой строке.

Sub Mid_Len_OddNoCharacters ()

‘ Использование функций LEN и MID для определения символов, появляющихся в нечетных позициях в текстовой строке

Sheets («Sheet1»). Активировать

Dim str As String, strOdd, i As Integer

‘присвоить текстовую строку в ячейке A2 («HELLO») переменной (str)

str = ActiveSheet.Range («A2»). Value

‘цикл через каждый символ — функция vba Len определяет длину или количество символов в текстовой строке

For i = 1 To Len (str)

‘проверить позицию нечетного числа

If i Mod 2 = 1 Then

‘ вернуть символ в позиции нечетного числа и добавить его в строковая переменная srtOdd

‘Функция vba Mid извлекает 1 символ из str, начиная с символа с номером i

strOdd = strOdd & Mid (str, i, 1)

End If

MsgBox strOdd

‘введите строку (переменную srtOdd), содержащую нечетные позиции символы в ячейке A3 («HLO»)

Range («A3»). Value = strOdd

End Sub

Пример — использование функций LEFT, LEN и MID для возврата инициалов полного имени.

Sub Left_Mid_Len_InitialsOfName ()
‘Использование функций LEFT, LEN и MID для возврата urn инициалы полного имени.
‘возвращать инициалы из текстовой строки, содержащей полное имя, состоящее из нескольких слов
‘ рассматривать строку, содержащую имя, отчество (имена) и фамилию, имеющие между пробелами — вернуть инициалы полного имени, где после каждого инициала ставится точка и пробел.

Sheets («Sheet1»). Активировать

Dim strName As String, strInitials As String

Dim i As Integer

‘назначить переменную strName текстовой строке — имя, отчество (имена) и фамилию с между пробелами:

‘strName = «Alec Thomas Stone Hanson»

strName = ActiveSheet.Range («A6»). Value

‘цикл по каждому символу:

For i = 1 To Len (strName)

‘ если первый символ

Если i = 1, то

‘, если первый символ не является пробелом, он будет начальным:

If Left (strName, i) «» Затем

‘добавить точку после первого начального

strInitials = Left (st rName, 1) & «.»

End If

‘if NOT first character

Else

‘если любому символу после первого символа предшествует пробел, он будет начальным:

Если Mid (strName, i — 1, 1) = «» And Mid (strName , i, 1) «» Then

‘определяет, если первый начальный

If Len (strInitials)

‘добавить точку после первого инициала

strInitials = Mid (strName, i, 1) & «.»

‘ для нескольких инициалов:

Else

‘для нескольких инициалов, добавьте к предыдущим инициалам:

strInitials = strInitials & «» & Mid (strName, i, 1) & «. «

End If

Next i

‘конвертирует все инициалы в верхний регистр:

strInitials = UCase (strInitials)

‘ возвращает «A. TSH «и введите строковую переменную strInitials в ячейку A7

MsgBox strInitials

Range (» A7 «). Value = strInitials

‘возвращает 11–4 буквы, 4 точки и 3 пробела

MsgBox Len (strInitials)

End Sub

Функция REPLACE (рабочий лист)

Функция REPLACE на рабочем листе заменяет часть текстовой строки новой текстовой строкой на основе указанного количества символов и начиная с указанной позиции. Синтаксис: REPLACE (old_text, start_number, number_of_chars, new_text) . Необходимо указать все аргументы. old_text — это текстовая строка, в которой вы хотите заменить новый текст. Аргумент start_number — это позиция символа в old_text, который вы хотите заменить ( т.е. позиция первого символа, с которого замена должен начаться). Позиция — это номер символа, первый символ — это цифра 1 и увеличивается вправо. number_of_chars — это количество символов, которые будут заменены в old_text (на new_text). new_text — это текстовая строка, которая заменяет символы в old_text.

Пример — использование функции замены рабочего листа в коде vba для удаления пробелов, превышающих указанное число, в строке

Функция DeleteBlankSpaces (str As String, iSpaces As Integer) As String
‘с использованием функции замены рабочего листа в коде vba для удаления пробелов, превышающих указанное число, внутри string (str)
‘используйте этот код для сокращения нескольких пробелов в строке до указанного количества пробелов (iSpaces)
‘ этот код также может удалять ВСЕ пробелы в строке
‘этот код также может преобразовывать несколько пробелов в один пробел в строке, аналогично функции обрезки рабочего листа, за исключением того, что при использовании обрезки удаляются ВСЕ пробелы перед первым (непробельным) символом.

Dim n As Integer, counter As Integer

counter = 0

‘начать с последнего символа в строке и перейти к первому

For n = Len (str) To 1 Step -1

‘, если символ — это пробел

If Mid (str, n, 1) = «» Тогда

‘увеличивает счетчик на 1

counter = counter + 1

‘если пустое пространство (а) равно или меньше указанного числа

Если counter

‘ go к следующему символу, т.е.. next n

GoTo пропустить

‘если пробел (а) превышает указанное число, то удалить

Else

‘с использованием функции Replace на листе для замены строкой нулевой длины

str = Application.Replace (str, n, 1, «»)

End If

‘, если символ НЕ является пробелом, перейти к следующему символу, т.е. следующий n

Else

‘восстановить до 0, если не пустой символ

counter = 0

End If

skip:

Next n

‘функция возвращает последнюю строку после удаления лишних последовательных пробелов пробелы

DeleteBlankSpaces = str

Конечная функция

Sub DelBlSp ()
‘удаляет лишние последовательные пробелы в строке (str), т.е. сверх указанного числа
‘Примечание: этот код не гарантирует единый номер. пробелов, но удаляет ТОЛЬКО лишние пробелы

Sheets («Sheet1»). Активировать

Dim strText As String, iSpaces As Integer

‘назначить строку в ячейке A10 («Указать максимальное количество пробелов в тексте.») активного листа переменной (strText)

strText = ActiveSheet.Range ( «A10»). Значение

‘указывает максимальное количество последовательных пробелов, которые следует сохранить в строке — эта процедура удалит пробелы, превышающие это указанное число

‘ назначьте это максимальное число переменной iSpaces

iSpaces = 2

‘strText содержит 4 пробела после «Укажите», 4 пробела после «пробелов «& 3 пробела после» текста «, все из которых уменьшены до 2 пробелов

‘введите последнюю строку в ячейку A11 (» Укажите максимальное количество пробелов в тексте. «) Активного листа — вызов Функция DeleteBlankSpaces и передает аргументы строки (strText) и указанные пробелы (iSpaces)

ActiveSheet.Range («A11»). Value = DeleteBlankSpaces (str Текст, iSpaces)

End Sub

Пример — убедитесь, что единообразный номер последовательных пробелов в строке, где присутствуют существующие пробелы

Функция UniformBlankSpaces (str As String, iSpaces As Integer) As String
‘Обеспечивает единый номер. последовательных пробелов в строке, где присутствуют существующие пробелы

Dim n As Integer, counter As Integer

‘counter используется для определения нет. последовательных пробелов

counter = 0

‘начать с последнего символа в строке и перейти к первому

Для n = Len (str) To 1 Step -1

‘, если символ является пробелом

Если Mid (str, n, 1) = «» Затем

‘увеличить счетчик на 1

counter = counter + 1

‘, если пробел (а) меньше указанного числа

If counter

‘, если первый символ строки является пробелом

Если n = 1 Then

‘добавить пробел

str = Left (str, 1) & «» & Right (str, Len (str) — n)

‘если символ, предшествующий пробелу в n, НЕ является пробелом

ElseIf Mid (str, n — 1, 1) «» Then

‘ добавить пробел

str = Mid (str, 1, n) & «» & Right (str, Len (str) — n)

‘, если предшествующий символ пробел в n также является пробелом.

Else

‘перейти к следующему символу, т.е. следующий n

GoTo skip

End If

‘если пустое пространство (а) превышает указанное число, затем удалить

ElseIf counter> iSpaces Then

‘с использованием функции Replace на листе для замены строкой нулевой длины

str = Application.Replace ( str, n, 1, «»)

End If

‘если символ НЕ является пробелом, перейти к следующему символу, т.е. next n

Else

‘восстанавливает счетчик до 0, если символ непустой

counter = 0

End If

skip:

Next n

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

UniformBlankSpaces = str

Конечная функция

Sub UniformBlSp ()
‘убедитесь, что единообразный нет. последовательных пробелов в строке, где присутствуют существующие пробелы
‘Примечание: этот код гарантирует единый номер. пробелов, где также присутствуют ведущие и конечные пробелы

Sheets («Sheet1»). Активировать

Dim strText As String, iSpaces As Integer

strText = ActiveSheet .Range («A10»). Value

‘указывает единообразное количество последовательных пробелов, которые необходимо сохранить в строке — эта процедура удалит/добавит пробелы, превышающие/меньшие этого указанного числа

‘присвоить этот номер переменной iSpaces

iSpaces = 2

‘ ввести последнюю строку в ячейку A12 («Указать макс. пробелы в тексте. «) — вызвать функцию UniformBlankSpaces и передать аргументы строки и указанные пробелы

ActiveSheet.Range (» A12 «). Значение = UniformBlankSpaces (strText, iSpaces)

End Sub

Функция REPLACE (VBA)

Функция замены vba используется для возврата строки, в которой указанная подстрока заменяется указанное количество раз другой указанной подстрокой. Синтаксис: Replace (выражение, найти, заменить, начать, подсчитать, сравнить) . Необходимо указать аргументы выражения, найти и заменить, в то время как аргументы start, count и compare необязательны.

Выражение аргумент — это строка, в которой заменяется определенная подстрока. Для строки выражения нулевой длины возвращается строка нулевой длины, а для выражения Null функция выдаст ошибку. Аргумент find указывает подстроку, которую необходимо заменить. Если найти подстроку нулевой длины, то возвращается копия выражения. Аргумент replace указывает ту «другую подстроку», которая заменяет (т. Е. Подстроку замены). Подстрока замены нулевой длины приводит к удалению всех вхождений find из выражения. Аргумент start указывает позицию (т.е. номер символа) в выражении, с которой вы хотите начать поиск подстроки ‘find’. Если опущено, по умолчанию будет 1 (т.е. поиск начнется с позиции первого символа). Строка, возвращаемая функцией Replace, начинается с этой начальной позиции до последнего символа строки выражения после замены (замен). Если указать начальную позицию, превышающую длину выражения, будет возвращена строка нулевой длины. Аргумент count определяет количество замен подстроки, которое вы хотите сделать. Если не указывать это значение, по умолчанию будет установлено значение -1, что сделает все возможные замены. Указание нуля для счетчика не приведет к замене и вернет копию выражения. Аргумент compare определяет тип сравнения, который следует использовать для оценки подстрок — здесь можно указать числовое значение или константу, как подробно описано ниже.

Вы можете указать следующие аргументы для аргумента сравнения : vbUseCompareOption (значение: -1) выполняет сравнение с использованием настройки параметра Вариант Сравнить заявление. vbBinaryCompare (значение: 0) выполняет двоичное сравнение — сравнение строк на основе двоичного порядка сортировки (в Microsoft Windows кодовая страница определяет порядок сортировки — при этом ANSI 1252 используется для английского и многих других языков. Европейские языки), в котором символы верхнего регистра всегда меньше символов нижнего регистра -> A vbTextCompare (значение: 1) выполняет текстовое сравнение — сравнения строк, которые не основаны на порядке сортировки текста с учетом регистра -> (A = a) vbDatabaseCompare (значение: 2) выполняет сравнение на основе информации в вашей базе данных (может использоваться только в Microsoft Access). Если вы не укажете аргумент сравнения, сравнение будет выполнено на основе определенного оператора Option Compare. Оператор Option Compare (например, Option Compare Binary или Option Compare Text) может использоваться для установки метода сравнения — вы должны указать «Option Compare Binary» или «Option Compare Text» на уровне модуля перед любой процедурой. Если оператор Option Compare не указан, по умолчанию используется двоичный метод сравнения текста.

Пример — использование функции замены vba.

Sub Replace_vbaFunc ()
‘с помощью функции замены vba

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

‘найти все вхождения «s» для замены:

strFind = «s»

‘ установить заменяемую строку как нулевую длину:

strReplace = «»

‘———————— —-

str = «Она продавала морские ракушки!»

‘возвращает 27:

MsgBox Len (str)

‘возвращает строку после удаления всех вхождений’ s ‘- «Она ждала ад!». Обратите внимание, что заглавная буква «S» не заменяется.

str = Replace (str, strFind, strReplace)

MsgBox str

‘ возвращает 22:

MsgBox Len (str)

‘————— ————-

str = «Она продавала морские ракушки!»

‘возвращает 27:

MsgBox Len (str)

‘возвращает строку после удаления всех вхождений’ s ‘- «he wa ea ea hell!».

‘ Обратите внимание, что заглавная буква ‘S’ также заменяется, потому что текстовое сравнение было выполнено путем установки значения аргумента сравнения на vbTextCompare (значение: 1).

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

MsgBox str

‘возвращает 21:

MsgBox Len (str)

‘—————————

str = «Она продавал морские ракушки! «

‘возвращает 27:

MsgBox Len (str)

‘ удаляя все вхождения ‘s’ из начальной позиции 8 — возвращает «elling ea hell!». Обратите внимание, что номер символа 8 является пробелом, и он также возвращается.

‘строка, возвращаемая функцией Replace, начинается с этой начальной позиции до последнего символа строки выражения.

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

MsgBox str

‘возвращает 16:

MsgBox Len (str)

‘———————- ——

str = «Она продавала морские ракушки!»

‘возвращает 27:

MsgBox Len (str )

‘Если указать начальную позицию, превышающую длину выражения, будет возвращена строка нулевой длины.

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

‘возвращает строку нулевой длины:

MsgBox str

‘ возвращает 0:

MsgBox Len (str)

‘———————- ——

str = «Она продавала морские ракушки!»

‘возвращает 27:

MsgBox Len (str )

‘Аргумент count указывает количество замен подстроки, которое вы хотите сделать. Если не указать это, по умолчанию будет установлено значение -1, что сделает все возможные замены.

‘, указав начальную позицию 8 и счетчик 2:

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

‘возвращает «elling ea shells!» после удаления’ s ‘дважды с начальной позиции 8.

MsgBox str

‘возвращает 18:

MsgBox Len (str)

‘ ——— ——————-

End Sub

Пример — манипулирование строками в Excel VBA — удаление числовых значений, удаление/добавление пробелов Пробелы, заглавные буквы.

Чтобы загрузить файл Excel с живым кодом, щелкните здесь.

Функция StringManipulation (str As String) As String

‘Манипуляция строкой с помощью vba и функций замены рабочего листа, функция Trim/Len/Right/Mid/Chr/Asc/Ucase.

‘ Этот код управляет текстом строки следующим образом:
‘1. удаляет числа из текстовой строки;
‘2. удаляет начальные, конечные и промежуточные пробелы (оставляет один пробел между словами);
‘3. добавляет пробел (если его нет) после каждого восклицательного знака, запятой, точки и вопросительного знака;
‘4. делает заглавными первую букву строки и первую букву слова после каждого восклицательного знака, точки и вопросительного знака;

Dim iTxtLen As Integer, iStrLen As Integer, n As Целое, i как целое, ansiCode как целое

‘————————— —

‘УДАЛИТЬ ЧИСЛА

‘ Функция vba Chr возвращает символ (строковый тип данных), идентифицированный с указанным кодом символа.

‘Excel использует набор символов ANSI для компьютеров Windows. В операционной среде Windows используется набор символов ANSI, тогда как Macintosh использует набор символов Macintosh, так что возвращаемый символ соответствует этому набору символов..

‘chr (48) to chr (57) представляют собой числа от 0 до 9 в кодах символов ANSI/ASCII

Для i = от 48 до 57

‘удалить все числа из текстовой строки с помощью функции замены vba

str = Replace (str, Chr (i), «»)

Next i

‘—————————

«УДАЛИТЬ ВЕДУЩИЕ, СЛЕДУЮЩИЕ И МЕЖДУ ПРОСТРАНСТВАМИ (ОСТАВЛЯТЬ ОДИН ПРОБЕЛ МЕЖДУ СЛОВАМИ)

» с помощью функции ОБРЕЗКИ рабочего листа. Примечание: функция TRIM удаляет пробел с кодом ANSI 32, не удаляет неразрывный пробел с кодом ANSI 160

str = Application.Trim (str)

‘—————————

‘ ДОБАВИТЬ ПРОБЕЛ (ЕСЛИ НЕТ) ПОСЛЕ КАЖДОГО Восклицательного знака, запятой, точки и вопросительного знака:

‘установить значение переменной (iTxtLen) на длину строки

iTxtLen = Len (str)

‘начать с последнего символа в строке и перейти к первому

For n = iTxtLen To 1 Step -1

‘ Chr (32) возвращает пробел; Chr (33) возвращает восклицательный знак; Chr (44) возвращает запятую; Chr (46) возвращает точку/точку; Chr (63) возвращает вопросительный знак;

Если Mid (str, n, 1) = Chr (33) или Mid (str, n, 1) = Chr (44) или Mid (str, n, 1) = Chr (46) или Mid (str, n, 1) = Chr (63) Then

‘проверьте, нет ли пробела после, кроме последнего символа

If Mid (str, n + 1, 1) Chr (32) And n iTxtLen Then

‘с использованием функций Mid & Right для добавления пробела — обратите внимание, что используется текущая длина строки

str = Mid (str, 1, n) & Chr (32) & Right (str, iTxtLen — n)

‘обновить строку length — увеличивается на 1 после добавления пробела (символа) — это позволяет правильно использовать функцию Right в строке выше

iTxtLen = iTxtLen + 1

End If

Конец Если

Далее n

‘————— ————-

‘УДАЛИТЬ ПРОБЕЛ (ПРИ НАЛИЧИИ) ПЕРЕД КАЖДЫМ восклицательным знаком, запятой, точкой и вопросительным знаком:

‘сбросить значение переменной до длины строки:

iTxtLen = Len (str)

‘ исключить первый символ

Для n = iTxtLen To 2 Step -1

‘Chr (32) возвращает пробел; Chr (33) возвращает восклицательный знак; Chr (44) возвращает запятую; Chr (46) возвращает точку; Chr (63) возвращает вопросительный знак;

Если Mid (str, n, 1) = Chr (33) или Mid (str, n, 1) = Chr (44) или Mid (str, n, 1) = Chr (46) или Mid (str, n, 1) = Chr (63) Then

‘проверьте наличие пробела перед

Если Mid (str, n — 1, 1) = Chr (32) Then

‘с использованием функции Replace для удаления пробела:

str = Application. Replace (str, n — 1, 1, «»)

‘опустить повторную проверку того же символа снова — положение n сдвигов (уменьшается на 1) из-за удаления символа пробела:

n = n — 1

End If

Next n

‘—————————

‘ ЗАГЛАВНЫМИ БУКВАМИ:

‘сделать первую букву строки и первую букву слова заглавными после каждого восклицательного знака, точки и вопросительного знака, а все остальные буквы — в нижнем регистре

iStrLen = Len (str)

For i = 1 To iStrLen

‘определить код ANSI каждого символа в строке: Excel использует символ ANSI набор для компьютеров Windows. В операционной среде Windows используется набор символов ANSI, тогда как в Macintosh используется набор символов Macintosh, поэтому возвращаемый символ соответствует этому набору символов.

‘Функция vba Asc возвращает соответствующий код символа (Integer тип данных) для строковой буквы — Asc (строка).

ansiCode = Asc (Mid (str, i, 1))

Выбрать регистр ansiCode

’97–122 — это коды ANSI, приравниваемые к прописным буквам от «a» до «z»

Case 97 to 122

‘символ № 3 и далее, чтобы определить, являются ли две предшествующие ему позиции восклицательным, точкой и вопросительным знаком

Если i> 2, то

‘заглавная буква, позиция которой 2 символа после (1 символ после, будет пробел, добавленный ранее) восклицательного знака, точки и вопросительного знака:

If Mid (str, i — 2, 1) = Chr (33) Или Mid (str, i — 2, 1) = Chr (46) или Mid (str, i — 2, 1) = Chr (63) Тогда

Mid (str, i, 1) = UCase (Mid (str, i, 1)) v>

End If

‘сделать первую букву строки заглавной:

ElseIf i = 1 Then

Mid (str, i, 1) = UCase (Mid (str, i, 1))

End If

‘если заглавная буква, перейти к следующему символу (т.е. . следующий i):

Case Else

GoTo skip

End Select

skip:

Далее i

‘————————— —

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

StringManipulation = str

Конечная функция

Sub Str_Man ()
‘манипулировать строкой

Sheets («Sheet2»). Активировать

Dim strText As String

‘ указать текстовую строку которым необходимо манипулировать («2manipulate text string8, с помощью кода vba. проверьте ячейку ниже! ok? спасибо 7.») и назначьте строковой переменной strText

strText = ActiveSheet.Range (» A2 «). Значение

‘введите изменяемую строку в ячейку A3 (« Обработка текстовой строки с помощью кода vba. Проверьте ячейку ниже! Хорошо? Спасибо. «) активного листа — вызовите функцию StringManipulation и передайте строковый (str) аргумент

ActiveSheet.Range (» A3 «). Value = StringManipulation (strText)

Конец подпрограммы

Пример. Замена всех вхождений подстроки в строковом выражении другой подстрокой с помощью функции замены vba.

Чтобы загрузить файл Excel с живым кодом, щелкните здесь.

Функция Replace_Str1 (var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer) As Variant

‘Заменяет все вхождения подстроки (vFind ) в строке (var) с другой подстрокой (vReplace) — с помощью функции замены vba.

Dim iPosn As Integer

‘position первого появления vFind внутри var:

iPosn = InStr (var, vFind)

‘, если vFind не найден в v ar

Если iPosn

‘возвращает строку var:

Replace_Str1 = var

‘если vFind найден в var

Else

‘ используйте функцию vba Replace, чтобы заменить все экземпляры vFind на vReplace

Replace_Str1 = Заменить (var, vFind, vReplace«, iOption)

End If

End Function

Sub ReplaceStr1 ()
‘Заменяет все вхождения подстроки ( vFind) в строке (var) с другой подстрокой (vReplace) — с помощью функции vba Replace.
‘эта процедура вызывает функцию Replace_Str1 и передает четыре аргумента строки (var), в которой указана конкретная подстрока (vFind ) заменяется другой подстрокой (vReplace) и типом сравнения (iOption), используемым для оценки подстрок

Sheets («Sheet3»). Активировать

Dim var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer

‘var — это строка, в которой выполняется поиск vFind и заменяется vReplace:

‘ var = «she Sells sea shellS»

var = ActiveSheet.Range (» A2 «). Значение

‘vFind — это строка для поиска в var &, которая будет заменена на vReplace:

vFind =» s «

‘vReplace — это строка, которая заменяет все экземпляры vFind внутри var:

vReplace = «?»

‘ ——— —

‘чтобы удалить все пробелы, используйте строку нулевой длины для vReplace

‘ vFind = «»

‘vReplace = «»

‘———-

‘ в сравнении без учета регистра, если вы укажете заменять вхождения «a» то экземпляры обоих «A» и «a» будут заменены.

‘для выполнения двоичного сравнения (с учетом регистра), используйте значение 0:

‘ iOption = 0

‘для выполнения сравнение текста (без учета регистра), используйте значение 1:

iOption = 1

‘Null: если переменная не содержит действительных данных — Значение Null могло быть явно присвоено переменной, или это могло быть результатом операции между выражениями, содержащими Null.

‘, если var равно Null, процедура выхода

Если IsNull (var), то

MsgBox «var is Null, exiting procedure»

Exit Sub

‘, если var не равно Null :

Else

‘, если vFind имеет значение Null или строку нулевой длины:

If IsNull (vFind) Or vFind = » «Затем

‘вернет var без каких-либо замен и процедуры выхода:

MsgBox» Либо vFind равен Null, либо имеет нулевую длину «

ActiveSheet.Range («A3») = var

Exit Sub

Else

‘, если var или vFind не равны Null, и vFind не имеет нулевой длины, замените все экземпляры из vFind («? he? ell?» ? ea? hell? «):

ActiveSheet.Range (» A3 «) = Replace_Str1 (var, vFind, vReplace, iOption)

End If

End Sub

Функции InStr и InStrRev (VBA)

Функция InStr возвращает позицию (номер символа), в которой строка впервые встречается внутри другой строки. Синтаксис: InStr (start, string, substring, compare) . Необходимо указать аргументы строки и подстроки, а аргументы start и compare необязательны.

Аргумент start указывает позицию (т. е. номер символа) в строке, с которой вы хотите начать поиск подстроки. Необходимо указать аргумент start, если аргумент сравнения должно быть указано. Если не указано, по умолчанию будет равно 1 (т. е. поиск начнется с позиции первого символа). sition, длина которого больше длины строки, вернет 0 (ноль), и если start содержит Null, произойдет ошибка. Аргумент string — это строковое выражение, в котором выполняется поиск подстроки. Функция возвращает 0, если строка имеет нулевую длину, и возвращает Null, если строка имеет значение Null. Аргумент substring — это строковое выражение, поиск по которому выполняется в строке, позиция которого будет возвращена функцией. Функция возвращает 0, если подстрока не найдена, возвращает начальное значение, если строка имеет нулевую длину, и возвращает Null, если подстрока равна Null. Аргумент compare указывает тип сравнения, который следует использовать для оценки строк — здесь можно указать числовое значение или константу, как подробно описано ниже..

Вы можете указать следующие аргументы для аргумента сравнения : vbUseCompareOption (value : -1) выполняет сравнение, используя настройку оператора Option Compare. vbBinaryCompare (значение: 0) выполняет двоичное сравнение — сравнение строк на основе двоичного порядка сортировки (в Microsoft Windows кодовая страница определяет порядок сортировки — при этом ANSI 1252 используется для английского и многих других языков. Европейские языки), в котором символы верхнего регистра всегда меньше символов нижнего регистра -> A vbTextCompare (значение: 1) выполняет текстовое сравнение — сравнения строк, которые не основаны на порядке сортировки текста с учетом регистра -> (A = a) vbDatabaseCompare (значение: 2) выполняет сравнение на основе информации в вашей базе данных (может использоваться только в Microsoft Access). Если вы не укажете аргумент сравнения, сравнение будет выполнено на основе определенного оператора Option Compare. Оператор Option Compare (например, Option Compare Binary или Option Compare Text) может использоваться для установки метода сравнения — вы должны указать «Option Compare Binary» или «Option Compare Text» на уровне модуля перед любой процедурой. Если оператор сравнения параметров не указан, по умолчанию используется двоичный метод сравнения текста.

Функция InStrRev возвращает позицию (номер символа), в которой строка впервые встречается в другой строке, начиная с конца этой другой строки. Синтаксис: InStrRev (строка, подстрока, начало, сравнение) . Используйте функцию InStrRev вместо InStr для поиска в обратном направлении. Необходимо указать аргументы строки и подстроки, а аргументы запуска и сравнения являются необязательными. Если start не указан, используется -1, что означает, что поиск начнется с позиции последнего символа. Все остальные объяснения синтаксиса остаются такими же, как в функции InStr.

Пример — использование функции InStr.

Sub InStrFunc ()
‘с использованием функции InStr

Dim str1 As String, str2 As String

‘———- ——-

str1 = «она продает морские ракушки»

str2 = «e»

‘возвращает 3 :

MsgBox InStr (str1, str2)

‘————— —

str1 = «она продает морские ракушки»

str2 = «e»

‘возвращает 6:

MsgBox InStr (4, str1, str2)

‘—————- —

str1 = «она продает морские ракушки»

str2 = «e»

‘возвращает 0, потому что начальное значение (24) больше длины str1 (20):

MsgBox InStr (24, str1, str2)

‘— —————

str1 = «»

str2 = «e»

‘возвращает 0, потому что str1 имеет нулевую длину:

MsgBox InStr (str1, str2)

‘ —— ————

str1 = «она продает морские ракушки»

str2 = «f»

‘возвращает 0, потому что str2 не найден:

MsgBox InStr (str1, str2)

‘——————

Dim str3 как вариант

str1 = «она продает морские ракушки»

str3 = Null

‘возвращает 1, указывая на подтип Null — поскольку str3 имеет значение Null:

MsgBox VarType (InStr (str1, str3))

‘——————

str1 = «Она продает морские ракушки»

str2 = «s»

‘возвращает 9 — тип сравнения текста по умолчанию Двоичный (с учетом регистра):

MsgBox InStr (2, str1, str2)

‘возвращает 5 — выполнение текстового сравнения, в котором тип сравнения не чувствителен к регистру:

MsgBox InStr (2, str1, str2, 1)

‘——————

End Sub

Пример. Замена всех вхождений подстроки в строковом выражении другой подстрокой с помощью функций InStr, Left & Mid.

Чтобы загрузить файл Excel с живым кодом, щелкните здесь.

Функция Replace_Str2 (var As Variant, vFind As Variant, vReplace As Variant) As Variant
‘Заменить все вхождения подстроки в строковом выражении другой подстрокой — с помощью функций InStr, Left & Mid.
‘ Заменяет все вхождения vFind в var на vReplace — с использованием функций InStr, Left & Mid.
‘При замене в этой процедуре учитывается регистр, т.е. если вы укажете заменять вхождения «a», то экземпляры «A» НЕ будут заменены.

Dim iFindLen As Integer

Dim iPosn As Integer

‘позиция первого появление vFind внутри var:

iPosn = InStr (var, vFind)

‘длина vFind, которая будет заменена на vReplace:

iFindLen = Len (vFind)

‘длина vReplace, которая заменит vFind:

iReplaceLen = Len (vReplace)

‘, если vFind не найден в var:

If iPosn

‘ возвращает строку var и процедуру выхода:

Replace_Str2 = var

Функция выхода

‘, если vFind найден внутри var:

Else

‘loop:

‘ замените vFind на vReplace внутри var — используйте функции Left и Mid для возврата строки. Отсутствие номеров символов из функции Mid вернет текстовую строку полностью от позиции начального номера до конца текстовой строки.

var = Left (var, iPosn — 1) & vReplace & Mid (var , iPosn + iFindLen)

‘позиция первого вхождения vFind в обновленной переменной, начиная поиск позиции с первого символа ПОСЛЕ последней замены (iPosn + iReplaceLen)

iPosn = InStr (iPosn + iReplaceLen, var, vFind)

‘, если vFind не найден в обновленной переменной var, выйти из цикла

Если iPosn = 0, то выйти из цикла

Loop

End If

‘возвращает последнюю строку:

Replace_Str2 = var

Конечная функция

Sub ReplaceStr2 ()
‘Заменяет все вхождения vFind в var на vReplace — с использованием функций InStr, Left & Mid.
‘ Замена будет чувствительна к регистру в этой процедуре, т.е. если вы укажете заменять вхождения «a», то экземпляры «A» НЕ будут заменены.

Sheets («Sheet3»). Активировать

Dim var As Variant, vFind As Variant, vReplace As Variant

‘var — это строка, в которой выполняется поиск vFind и заменяется vReplace:

‘var = «she Sells sea shellS»

var = ActiveSheet.Range («A6»). Value

‘ vFind — строка для поиска в var & который будет заменен на vReplace

vFind = «s»

‘vReplace — это строка, которая заменяет все экземпляры vFind внутри var:

vReplace = «?»

‘, если var имеет значение Null, процедура выхода:

If IsNull (var) Then

MsgBox «var is Null, exiting procedure»

Exit Sub

‘, если var не равно Null:

Else

‘, если vFind имеет значение Null или строку нулевой длины:

Если IsNull (vFind) или vFind = «» Тогда

‘вернуть var без замен и процедуры выхода

M sgBox «Либо vFind равен Null, либо имеет нулевую длину»

ActiveSheet. Диапазон («A7»). Value = var

Exit Sub

Else

‘, если var или vFind не равны Null, и vFind не имеет нулевой длины, замените все экземпляры vFind («? he Sell? ea? hellS»)

ActiveSheet.Range («A7»). Value = Replace_Str2 (var, vFind, vReplace)

End If

End Sub

Почему Len и VBA.Len возвращают разные результаты?

Когда я запускаю следующий макрос:

  Sub try () Dim num As Integer num = 123 MsgBox Len (num) MsgBox VBA  .Len (num) End Sub

Первый Msgbox отображает 2 , а второй Msgbox отображает 3 . Если я удалю первую строку с надписью Dim num As Integer , оба MsgBoxes отобразят 3 код>.

Кто-нибудь может объяснить, почему?

Это связано со способом что VB хранит целые числа и как функция Len () обрабатывает аргументы, не являющиеся строками.

Когда тип данных, который не является строкой, передается в функцию Len () , она возвращает номинальное количество байтов, используемых для хранения данных (VB использует 2 байта для хранения целого числа). См. Документацию по функции Len .

Функция Len () автоматически приведет к преобразованию переменной варианта (которая создается путем присвоения значения переменной без предварительного объявления его) в виде строки. Возврат не меняется из-за изменения распределения памяти (хотя и меняется; варианты требуют минимум 16 байтов дискового пространства). Поскольку неявно объявленная переменная на самом деле является вариантным типом, VB автоматически изменит ее тип в зависимости от ситуации. В этом случае Len () ожидает строку, поэтому VB делает переменную строкой.

Вы можете использовать Msgbox Len (Cstr (num )) , чтобы преобразовать целочисленную переменную в строку перед ее передачей в функцию Len , если вы намерены вернуть количество символов в вашем целочисленном значении..

Вы можете создать ваша собственная функция len:

  Функция CountChar (ByVal Mystring As String) Тусклый алфавит как Variantalphabet = Array («A», «a», «B», «b»,  «C», «c», «D», «d», «E», «e», «F», «f», «G», «g», «H», «h», _ »  I »,« i »,« J »,« j »,« K »,« k »,« L »,« l »,« M »,« m »,« N »,« n »,« O »  , «o», «P», «p», «Q», «q», _ «R», «r», «S», «s», «T», «t», «U»,  «u», «V», «v», «W», «w», «X», «x», «Y», «y», «Z», «z», 0, 1, 2,  3 _, 4, 5, 6, 7, 8, 9) j = 0For i = 0 До 61 '//Найдите алфавит и числа, затем замените на "-" Выполните, пока InStr (Mystring, алфавит (i)) Mystring = Заменить  (Mystring, алфавит (i), "-") j = j + 1 LoopNext iCountChar = jd = MsgBox (Mystring) Конечная функция

Строковые функции Excel VBA: LEFT, RIGHT, MID, LEN, REPLACE, INSTR, INSTRREV

Почему Len и VBA.Len возвращают разные результаты?

О проекте

Популярные статьи

Categories