Есть ли способ использовать Regex(или аналогичное решение) в формулах Excel?

Question

У меня есть электронная таблица, которая требует, чтобы я провел какое-то сопоставление, а именно произнесение списка облигаций, их тикеров (имен) и некоторых данных о них. Я хочу сопоставить их тикеры (имена), я знаю, как бы регулярное выражение соответствовало им, используя регулярное выражение ("[AZ]+ \d {2,4} - \w+ \w+"), но я не знаком с тем, как использовать их за пределами VBA (да, я предпочитаю не использовать VBA, люди, которые будут использовать это не знают VBA).

Я прочитал эту статью от Microsoft, так что я знаю, что этот вопрос(утверждение, а не ответ) неверен, но мне никогда не удавалось узнать, как реализовать регулярное выражение в Excel, особенно в формулах (возможно, в MATCH может быть).

Наконец, мой пример (только соответствующий столбец), чтобы сделать мой вопрос более конкретным, я хочу вернуть строки ячеек с тикерами:

JPMCC 2012-1 A1 

Prepay %
Loss %
Credit Support
ETC...

MLMTI 2014-6 B4

Prepay %
Loss %
Credit Support
ETC...

Ряды для возвращения:

JPMCC 2012-1 A1 
MLMTI 2014-6 B4

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

Также, если вы обнаружите, что использование вспомогательных столбцов полезно здесь, я открыт для этого, сейчас я просто хочу избежать VBA.

При необходимости я предоставлю дополнительную информацию.

Спасибо

ОБНОВИТЬ

Данные, в данном случае, включают в себя такие заголовки, как "Кредитная поддержка", «Совокупный убыток%», «Предоплата%» и т.д. Существуют десятки таких заголовков, но весь текстовый текст, цифры находятся в другом столбце.

Answer 1

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

Извлечь первое слово, разделенное пробелом:

B1: =FIND(" ", A1)
C1: =LEFT(A1, B1 - 1)

Проверьте, все ли первые слова не строчные:

D1: =EXACT(UPPER(C1), C1)

Извлечь второе слово, разделенное пробелом:

E1: =FIND(" ", A1, B1 + 1)
F1: =MID(A1, B1 + 1, E1 - B1 - 1)

Извлеките 4-значный год, если это возможно:

G1: =LEFT(F1, 4) - 0

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

H1: IFERROR(IF((B1+E1+LEN(C1)+LEN(F1)+G1+D1)*0=1,"",A1),"")

Answer 2

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

1. Добавить ссылку на «Регулярные выражения Microsoft VBScript 5.5»

Как использовать регулярные выражения (Regex) в Microsoft Excel как внутри ячейки, так и в циклах

2. Создайте функцию, которая принимает диапазон, а затем оценивает его с помощью RegEx.

Вызовите функцию из формулы Excel - пользователю не нужно знать VBA, только имя функции.

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

Или вы можете также создать кнопку или что-то, что вызывает VBA и выполняет фильтрацию. Это удовлетворяет требованию, что пользователь не должен знать VBA.

Answer 3

Я бы попробовал Расширенные фильтры - вы можете настроить таблицу критериев, используя подстановочные знаки, а затем указать на нее операцию фильтра. Кнопка «Дополнительно» находится на ленте данных в разделе «Сортировка и фильтрация» (в Excel 2013).

Здесь очень подробное прохождение:

Расширенные фильтры: удивительная альтернатива Excel Regex

Answer 4

Можно также объединить этот подход FIND/LEFT/LEN с формулой массива, чтобы получить ограниченную аппроксимацию соответствия класса строк, предоставляемого регулярным выражением, без использования VBA:

{= IF(FALSE; "Комментарий: возвращает название улицы по полному адресу, отыскивая '' с последующим номером"; LEFT(M14; SUM(IFERROR(FIND("" & {0; 1; 2; 3; 4; 5; 6; 7; 8; 9}; M14); 0))))}