Как в таблице Excel проверить текст на соответствие установленному шаблону

В языке Visual Basic существует один крайне полезный оператор, называемый Like. Он умеет проверять подобие (похожесть) двух текстовых строк или соответствие текстовой строки заданному шаблону. Такое может очень пригодиться при проверке введенных пользователем данных. Например, с его помощью легко можно определить, правильно ли был введен.

  • номер автомобиля (шаблон: буква – три цифры – две буквы);
  • ФИО (шаблон: фамилия + пробел + две буквы с точками);
  • номер паспорта (шаблон: четыре цифры на серию + шесть на номер);
  • почтовый индекс (шаблон: только шесть цифр);
  • номер банковского счета (шаблон: 20 цифр) и т.д.

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

Откройте редактор Visual Basic, нажав Alt+F11 или выбрав на вкладке Разработчик команду Редактор Visual Basic (Developer → Visual Basic Editor), вставьте новый модуль (меню Insert – Module) и скопируйте туда текст этой функции.

1
2
3
4
5
6
7
8
9
10
11
Function MaskCompare(txt As String, mask As String, CaseSensitive As Boolean) as Boolean
    If Not CaseSensitive Then
        txt = UCase(txt)
        mask = UCase(mask)
    End If
    If txt Like mask Then
            MaskCompare = True
        Else
            MaskCompare = False
    End If
End Function

Закройте редактор Visual Basic и вернитесь в Excel.

Теперь через Вставка → Функция (Insert → Function) в категории Определенные пользователем (User Defined) можно найти нашу функцию MaskCompare и воспользоваться ею. Синтаксис функции следующий: =MaskCompare(txt; mask; CaseSensitive), где txt – любой текст или ячейка с текстом, которую мы проверяем на соответствие маске; mask – набор символов, которые ищутся в проверяемом тексте.

Набор может содержать спецсимволы подстановки:

  • * – любое количество любых символов;
  • ? – один любой символ;
  • # – любая цифра (0–9);
  • [список_символов] – любой символ из списка;
  • [!список_символов] – все символы, кроме содержащихся в списке.

Case_Sensitive – необходимо ли учитывать регистр при проверке:

  • 1 – регистр символов учитывается;
  • 0 – регистр символов не учитывается.

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

  • ### – все числа от 0 до 999;
  • ###### – почтовый индекс (российский);
  • ????? – все слова из 5 букв;
  • * ?.?. – любая фамилия с инициалами через точку (Фамилия И.О.);
  • [АВЕКМНОРСТУХ]###[АВЕКМНОРСТУХ][АВЕКМНОРСТУХ] – автомобильный номер;
  • п*ин – все фамилии, начинающиеся на «п» и заканчивающиеся на «ин» (Пушкин, Пупкин…);
  • *[аостр]* – все слова, содержащие хотя бы одну из букв а, о, с, т, р;
  • *[!abcdefghijklmnopqrstuvwxyz]* – все слова, НЕ содержащие английских букв.

Top