Excel vba поиск в строке символа

Excel vba поиск в строке символа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск на листе Excel

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  В excel вставить текст в ячейку

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»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

— для обозначения символов *, ? и

. (т.е. чтобы искать в тексте вопросительный знак, нужно написать

?, чтобы искать именно звездочку (*), нужно написать

* и наконец, чтобы найти в тексте тильду, необходимо написать

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

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

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

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

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

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

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

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

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

VBA, поиск в столбце для конкретного символа, извлечение строки до этого символа

2 Kinchit [2013-05-16 15:10:00]

В определенном столбце я хочу найти конкретный символ в ячейках. say «(» или «/». После того, как этот символ найден в ячейке, я хочу извлечь часть с начала строка до точки, в которой этот символ найден, в соседней с ним ячейке.

например. несколько значений в столбце могут выглядеть так:

Результат будет выглядеть как

ПРИМЕЧАНИЕ. Значения ячейки в этом конкретном столбце не являются статическими, не имеют шаблона, могут содержать и другие специальные символы, не имеют определенной длины.

vba excel-vba excel

4 Решение chuff [2013-05-16 18:13:00]

Этот вопрос хорошо подходит для регулярных выражений. Следующая функция возвращает позицию символа, предшествующего первому совпадению простого шаблона регулярного выражения в заданной строке. Если совпадение не найдено, функция возвращает длину строки. Функция может быть объединена с функцией LEFT для извлечения текста, предшествующего совпадению. (Использование LEFT необходимо, потому что для простоты эта функция не реализует субматрицы.)

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

Нарушение шаблона соответствия » (|/| -| *» :

Чтобы узнать больше о регулярных выражениях, см. этот учебник по регулярному выражению, один из многих доступных в Интернете.

Чтобы функция работала, вам необходимо установить ссылку на Microsoft VBScript Regular Expressions 5.5. Для этого выберите Инструменты/Ссылки из VBA IDE и проверьте этот элемент, который будет хорошо расположен по длинному списку ссылок.

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

Что-то вроде этого будет работать:

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

Это должно сработать для вас:

Он не удаляет конечные пробелы в конце, но простое дополнение к sub может изменить это, если вы захотите. Удачи.

Сложение: Вы можете получить строку последней ячейки с данными в столбце и использовать ее в своем диапазоне (см. Ниже):

1 dnLL [2013-05-16 15:34:00]

Вы можете использовать функцию Split() . Вот пример:

Просто сделайте то же самое для любого другого персонажа, которого хотите разбить. Подробнее об этом на MSDN: http://msdn.microsoft.com/fr-fr/library/6x627e5f%28v=vs.80%29.aspx

Другая (лучше?) альтернатива, которую я вижу, будет использовать InStr() с Left() . InStr() возвращает позицию первого найденного совпадения. Тогда вам просто нужно обрезать строку. Вот пример:

Функция InStr

Возвращает значение типа Variant (Long), определяющее положение первого вхождения одной строки в другую.

Функция InStr имеет следующие аргументы:

Необязательный аргумент. Числовое выражение, которое задает начальное положение для каждого поиска. Если аргумент не задан, поиск начинается с первого символа. Если аргумент начало содержит значение NULL, возникает ошибка. Если задан аргумент сравнение, аргумент начало является обязательным.

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

Обязательный аргумент. Искомое строковое выражение.

Необязательный аргумент. Указывает тип сравнение строк. Если функция сравнить имеет значение null, возникает ошибка. Если аргумент » Сравнение«опущен, тип сравнения определяется параметром «сравнить «. Укажите допустимый LCID (LocaleID), чтобы использовать в сравнении правила, зависящие от языкового стандарта.

Совет: В Access 2010 построитель выражений включает функцию IntelliSense, которая указывает требуемые аргументы.

Аргумент сравнение может принимать следующие значения:

Выполняется сравнение с помощью параметра инструкции Option Compare.

Выполняется двоичное сравнение.

Выполняется текстовое сравнение.

Только в Microsoft Office Access 2007. Выполняется сравнение на основе сведений из базы данных.

строка1 является пустой

строка1 равна NULL

строка2 является пустой

строка2 равна NULL

строка2 не найдена

строка2 найдена в строке1

Позиция найденного соответствия

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

Читать еще:  Excel автоматическая высота строки по содержимому

Использование функции InStr в выражении. Функцию InStr можно использовать в любых выражениях. Например, если требуется определить позицию первой точки (.) в поле, которое содержит IP-адрес (IPAddress), можно использовать функцию InStr для его поиска:

Функция InStr проверяет каждое значение в поле IPAddress и возвращает позицию первой точки. Следовательно, если значение первого октета IP-адреса равно 10., функция возвращает значение 3.

Можно использовать другие функции, использующие результат функции InStr, для извлечения значения октета IP-адреса, который предшествует первой точке, например:

В этом примере функция InStr(1,[IPAddress],».») возвращает позицию первой точки. В результате вычитания 1 определяется количество знаков, предшествующих первой точке, в данном случае — 2. Затем функция Left получает эти символы из левой части поля IPAddress, возвращая значение 10.

Использование функции InStr в коде VBA

Примечание: В примерах ниже показано, как использовать эту функцию в модуле Visual Basic для приложений (VBA). Чтобы получить дополнительные сведения о работе с VBA, выберите Справочник разработчика в раскрывающемся списке рядом с полем Поиск и введите одно или несколько слов в поле поиска.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Место символа в строке

Поиск символа в строке
Здравствуйте. Мне необходимо проверить выделенный фрагмент текста на наличие в конце точки. Если.

Определение положения символа в строке
Доброго дня. Помогите с маленьким вопросом. Есть строка (текст в ячейке). Текст может меняться, но.

Проверка и замена последнего символа в строке
Есть код, который ищет в тексте буквы "и" и заменяет их на мне нужную (в данном случае "ы"). Как.

Проверка наличия символа в строке + подсчет
Всем, Здравствуйте. Помогите разобраться в следующем примере Имеется таблица (excel 2003) с.

Оставить в строке только по одному экземпляру каждого символа
Задание: нужно оставить в строке только по одному экземпляру каждого символа (повторения учитывать.

Можно обе

А лучше вторую

аххах..это точно..ахах..объясни хотя бы что значит » & Space(3) & _InStr(i, f, «о», 1) ?
а возможно такое задание через циклы While — Wend и
Do – Loop или Do while – Loop или Do – Loop while написать?
P.S. кот породы двортерьер))

Добавлено через 2 минуты
на f я заменила данную строку букв)

Да. Вы можете оформить по своему вкусу.
Смотрите. Вот так для меня звучит хороший любительский код (и у меня так иногда получается, но редко )
http://www.youtube.com/watch?v=SJUQD6Rr2M8&feature=youtu.be

Добавлено через 4 минуты
А вот так для меня звучит (и смотрится) хороший профи-код.
https://www.youtube.com/watch?v=jbPv0dWsDQw
А если Вы это будете делать через While — боюсь, от стука по железяках я стану глухой.

Добавлено через 4 минуты
Не так, а вот так:
For i = InStr(1, «аывраводлго», «о», 1) + 1 To Len(«аывраводлго»)
Я уже это говорил выше. » От i равняется номер размещеня первого символа (искать от 1) +1 до значения длины всего слова. » Где-то так.

[QUOTE=Igor_Tr;5297337]Да. Вы можете оформить по своему вкусу.
Смотрите. Вот так для меня звучит хороший любительский код (и у меня так иногда получается, но редко )
http://www.youtube.com/watch?v=SJUQD6Rr2M8&feature=youtu.be

Добавлено через 4 минуты
А вот так для меня звучит (и смотрится) хороший профи-код.
https://www.youtube.com/watch?v=jbPv0dWsDQw
А если Вы это будете делать через While — боюсь, от стука по железяках я стану глухой.

прикольно)просто задание такое,что эту задачу надо через цикл написать(
P.S. я не такая старая чтоб со мной на Вы)так что давай без Вы))

Sorry. Привычка. Так меня, к сожалению, приучили. И отвыкать что-то не хочу. Еще и с дамой. (самому страшно, какой я галантный )
То, что они задали — пусть Вас не волнует. Им главное, что б Вы понимали, что пишете. Что б Вам понять — нужно всего-навсего разобраться с двумя функциями VBA. Это InStr (в первую очередь) и MsgBox. Они абсолютно не сложные, но ооочень важные в vba. C функцией space, думаю, проблем нет. Если да — поэкспериментируйте. Поставте разные значени (space(10), space(1), space(20)) — и все увидите. Все. Иду машину забирать из ремонта. Накидайте Ваши вопросы — если не я, другой кто-то может подключиться. Но, думаю, завтра еще до обеда я буду на месте. Собирайте.

Добавлено через 8 часов 45 минут
To Ksyun. Было немного времени. Здесь с циклами, и результат в массив. Но в общем, есть куча других способов, как это все решить (можна и через Reg. expressions). Пробуйте. Удачи.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector