Поиск символа в строке в vba: Практическое руководство. Поиск в строке — Visual Basic

Поиск с конца строки в Excel

Готовые решения » Надстройка VBA-Excel » Поиск с конца строки

Функция КОНЕЦСТРОКИ из надстройки поможет быстро вернуть конец строки после заданного текста.

Добавить в Excel

Описание функции

К сожалению, в Excel нет встроенной функции, которая возвращает конец строки после заданного текста. Хотя можно решить эту задачу и стандартными функциями, но не всегда их можно на память или логику быстро воспроизвести.

Вот этот набор функций, не очень то очевидно, согласитесь:

 =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«Заданный текст»;A1;1)-ДЛСТР(«Заданный текст»))

Для удобства в надстройку VBA-Excel добавлена функция КОНЕЦСТРОКИ. Она выполняет поиск заданного текста с конца строки и возвращает текст от искомой позиции до конца строки. И интерфейс у нее намного понятнее:

Функция =КОНЕЦСТРОКИ(ТЕКСТ; НАЙТИ; [Старт]; [Регистр]

) имеет 4 аргумента:

• ТЕКСТ — Исходный текст, в котором необходимо найти начало строки.
• НАЙТИ — Подстрока, до которой будет возвращаться начало строки
• [Старт] — Необязательный параметр. Позиция внутри исходного текста, с которой начинается поиск подстроки. По умолчанию параметр равен 1.
• [Регистр] — Необязательный аргумент, указывающий необходимость учета регистра. По умолчанию равен 1 — регистр учитывается. Укажите значение 0 если не хотите учитывать регистр при сравнении.

Пример 1

Найти текст до слова СТОП. Обратите внимание не важно в какой части текста находится заданное слово.

Пример 2

Чтобы вернуть текст без первого слова укажите пробел в параметре НАЙТИ.

Пример 3

Можно начинать поиск с заданной позиции (аргумент Старт). Может понадобиться в случае если стоп-слово встречается не один раз.

Пример 4

В предыдущих примерах регистр искомого текста учитывался. Для того чтобы заглавные и строчные буквы НЕ учитывались при поиске, установите параметр Регистр = 0.

Код на VBA

Function КОНЕЦСТРОКИ(ByVal ТЕКСТ As String, ByVal НАЙТИ As String, _
                    Optional ByVal Старт As Long = 1, Optional ByVal Регистр As Long = 1) As String
    If Регистр = 1 Then
        КОНЕЦСТРОКИ = Right(ТЕКСТ, Len(ТЕКСТ) - InStr(Старт, ТЕКСТ, НАЙТИ) - Len(НАЙТИ) + 1)
    Else
        КОНЕЦСТРОКИ = Right(ТЕКСТ, Len(ТЕКСТ) - InStr(Старт, ТЕКСТ, НАЙТИ, vbTextCompare) - Len(НАЙТИ) + 1)
    End If
End Function

Надстройка
VBA-Excel

Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.

Добавить в Excel

Рекомендуем к прочтению

Формула СЦЕПИТЬДИАПАЗОН

Подсчет количества определенных символов ячейке excel

Сравнение текста с шаблоном в Excel

Как разделить текст в Excel на подстроки

Извлечь текст из строки Excel

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

Комментарии:

Please enable JavaScript to view the comments powered by Disqus. comments powered by Disqus

Поиск на листе Excel, примеры использования Find на VBA

Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.

 

Поиск перебором значений

Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:

Sheets("Данные").Select
For y = 1 To Cells.SpecialCells(xlLastCell).Row
    If Cells(y, 1) = "123" Then
        Exit For
    End If
Next y
MsgBox "Нашел в строке: " + CStr(y)

Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т. п.

Поиск функцией Find

Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:

Sheets("Данные").Select
Set fcell = Columns("A:A").Find("123")
If Not fcell Is Nothing Then
    MsgBox "Нашел в строке: " + CStr(fcell.Row)
End If

Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.

Полностью синтаксис оператора поиска выглядит так:

Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

What — Строка с текстом, который ищем или любой другой тип данных Excel

After — Ячейка, после которой начать поиск.

Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.

LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).

LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).

SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)

SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)

MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)

MatchByte

— Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)

SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.

Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).

Примеры поиска функцией Find

Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", LookIn:=xlValues)
  Do While Not c Is Nothing
    c.Value = "qwe"
    Set c = .FindNext(c)
  Loop
End With

Обратите внимание: Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:

Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.

Пример 3: Продолжение поиска с использованием Find с параметром After.

With Worksheets(1).Range("A1:A50")
  Set c = .Find("asd", lookin:=xlValues)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Bold = True
      Set c = .Find("asd", After:=c, lookin:=xlValues)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.

Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)

lLastRow = Cells.SpecialCells(xlLastCell).Row
lLastCol = Cells.SpecialCells(xlLastCell).Column
Application.FindFormat.Font.Italic = True
With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol))
  Set c = .Find("", SearchFormat:=True)
  Do While Not c Is Nothing
    c.Font.Italic = False
    Set c = . Find("", After:=c, SearchFormat:=True)
  Loop
End With

Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)

Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.

Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.

Свойство FindFormat можно задавать разными способами, например, так:

With Application.FindFormat.Font 
  .Name = "Arial" 
  .FontStyle = "Regular" 
  .Size = 10 
End With

Поиск последней заполненной ячейки с помощью Find

Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.

Пример 5: Найти последнюю колонку и столбец, заполненные данными

Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious)
If Not c Is Nothing Then
  lLastRow = c.Row: lLastCol = c.Column 
Else
  lLastRow = 1: lLastCol = 1
End If
MsgBox "lLastRow=" & lLastRow & " lLastCol=" & lLastCol

В этом примере используется UsedRange, который так же как и SpecialCells возвращает все используемые ячейки, в т. ч. и те, что были использованы ранее, а сейчас пустые. Функция Find ищет ячейку с любым значением с конца диапазона.

Поиск по шаблону (маске)

При поиске можно так же использовать шаблоны, чтобы найти текст по маске, следующий пример это демонстрирует.

Пример 6: Выделить красным шрифтом ячейки, в которых текст начинается со слова из 4-х букв, первая и последняя буквы «т», при этом после этого слова может следовать любой текст.

With Worksheets(1).Cells
  Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstResult = c.Address
    Do
      c.Font.Color = RGB(255, 0, 0)
      Set c = .FindNext(c)
      If c Is Nothing Then Exit Do
    Loop While c.Address <> firstResult
  End If
End With

Для поиска функцией Find по маске (шаблону) можно применять символы:
* — для обозначения любого количества любых символов;
? — для обозначения одного любого символа;
~ — для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)

Поиск в скрытых строках и столбцах

Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas

Поиск даты с помощью Find

Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:

• Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не «01.03.2018»
• В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas

Приведу несколько примеров поиска даты.

Пример 7: Найти текущую дату на листе независимо от формата отображения даты.

d = Date
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Пример 8: Найти 1 марта 2018 г.

d = #3/1/2018#
Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

Искать часть даты — сложнее. Например, чтобы найти все ячейки, где месяц «март», недостаточно искать «03» или «3». Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел — это выбрать формат в котором месяц прописью для ячеек с датами и искать слово «март» в xlValues.

Тем не менее, можно найти, например, 1 марта независимо от года.

Пример 9: Найти 1 марта любого года.

d = #3/1/1900#
Set c = Cells. Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart)
If Not c Is Nothing Then
  MsgBox "Нашел"
Else
  MsgBox "Не нашел"
End If

 

Промежуточная функция (Visual Basic для приложений)

Редактировать

Твиттер LinkedIn Фейсбук Эл. адрес

• Статья
• 30.03.2022
• 2 минуты на чтение

Возвращает вариант ( String ), содержащий указанное количество символов из строки.

Синтаксис

Середина ( Строка , Start , [ Длина ])

середина Функция Синтаксис имеет эти названные аргументы:

444443

Часть

.	Описание
строка	Обязательно. Строковое выражение, из которого возвращаются символы. Если строка содержит Null, возвращается Null .
старт	Требуется; Длинный. Позиция символа в строке , с которой начинается берущаяся часть. Если start больше, чем количество символов в строке , Mid возвращает строку нулевой длины («»).
длина	Дополнительно; Вариант ( Длинный ). Количество возвращаемых символов. Если опущено или если в тексте меньше символов длины (включая символ на start ), возвращаются все символы с позиции start до конца строки.

Чтобы определить количество символов в строке , используйте функцию Len .

Примечание

Используйте функцию MidB с байтовыми данными, содержащимися в строке, как в языках с двухбайтовым набором символов. Вместо указания количества символов аргументы задают количество байтов. Для примера кода, который использует MidB , см. второй пример в теме примеров.

Пример

В первом примере функция Mid используется для возврата заданного количества символов из строки.

 Dim MyString, FirstWord, LastWord, MidWords
MyString = "Mid Function Demo" ' Создать текстовую строку.
FirstWord = Mid(MyString, 1, 3) ' Возвращает "Середина".
LastWord = Mid(MyString, 14, 4) ' Возвращает "Демо".
MidWords = Mid(MyString, 5) ' Возвращает «Демонстрация функции».
 

Второй пример использования MidB и определяемая пользователем функция ( MidMbcs ), чтобы также возвращать символы из строки. Разница здесь в том, что входная строка имеет формат ANSI, а длина указана в байтах.

 Функция MidMbcs (ByVal str as String, start, length)
    MidMbcs = StrConv(MidB(StrConv(str, vbFromUnicode), начало, длина), vbUnicode)
Конечная функция
Dim MyString
MyString = "AbCdEfG"
' Где "A", "C", "E" и "G" являются DBCS и "b", "d",
' и "f" являются SBCS. 
МояНоваяСтрока = Середина(МояСтрока, 3, 4)
' Возвращает "CdEf"
МояНоваяСтрока = MidB(МояСтрока, 3, 4)
' Возвращает "бК"
MyNewString = MidMbcs(MyString, 3, 4)
' Возвращает "бКд"
 

См. также

• Функции (Visual Basic для приложений)

Поддержка и отзывы

У вас есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы Office VBA, чтобы узнать, как вы можете получить поддержку и оставить отзыв.

Левая функция (Visual Basic для приложений)

Редактировать

Твиттер LinkedIn Фейсбук Эл. адрес

• Статья
• 30.03.2022
• 2 минуты на чтение

Возвращает вариант ( String ), содержащий указанное количество символов с левой стороны строки.

Синтаксис

Слева ( строка , длина )

Синтаксис функции Left имеет следующие именованные аргументы:

Часть	Описание
строка	Обязательно. Строковое выражение, из которого возвращаются крайние левые символы. Если строка содержит Null, возвращается Null .
длина	Требуется; Вариант ( Длинный ). Числовое выражение, указывающее, сколько символов нужно вернуть. Если 0, возвращается строка нулевой длины («»). Если больше или равно количеству символов в строка , возвращается вся строка.

Чтобы определить количество символов в строке , используйте функцию Len .

Примечание

Используйте функцию LeftB с байтовыми данными, содержащимися в строке.