Функции Excel находят образец строки в ячейке
У меня есть личные документы в отчетах, которые я должен найти в одной ячейке. Жаль, что строка в ячейке, которая скрывает этот идентификатор, может быть чем угодно, идентификатор может быть в начале, в конце, где угодно, но он есть. Единственное, что я знаю, это шаблон "пробел, буква, буква, число, число, число, число, число, число, пробел". Jike DB544345
Я искал правильное слово для этой "маски", но не смог найти ответ. Спасибо за помощь.
4 ответа
Поскольку комментариев много, я создал минимальный пример, который может представлять, с чем имеет дело OP:
A1: 123456789 DB544345 asdfg asdfghjk
A2: creating dummy data is a DB544345 pain
A3: DB5443456 and soething else
проанализировал копию этого в ColumnB с Text To Columns (с пробелом в качестве разделителя) и применил:
=IFERROR(IF(AND(LEN(B1)=8,CODE(LEFT(B1))>64,CODE(LEFT(B1))<91,CODE(MID(B1,2,1))>64,CODE(MID(B1,2,1))<91,ISNUMBER(RIGHT(B1,6)*1),RIGHT(B1,6)*1>99999),B1,""),"")
в K1, скопировал это в P1 и затем в K1:P1 вниз.
Я думаю, что это будет работать, если мы предположим, что SPACE в начале и в конце просто отличить идентификатор от остальной части строки; следовательно, не будет присутствовать, если идентификатор был в начале или конце строки. Эта формула нечувствительна к регистру. Если требуется чувствительность к регистру, мы могли бы сделать сравнение кодов символов.
=LOOKUP(2,1/((LEFT(myArr,2)>="AA")*(LEFT(myArr,2)<="ZZ")*(LEN(myArr)=8)*ISNUMBER(-RIGHT(myArr,6))),myArr)
Где myArr относится к:
=TRIM(MID(SUBSTITUTE(TRIM(Sheet2!A1)," ",REPT(" ",99)),(ROW(INDIRECT("1:10"))-1)*99+1,99))
Если myArr изначально определен с помощью курсора в B1, ссылаясь на A1, как показано, он будет корректироваться для ссылки на ячейку в столбце слева от ячейки, в которой появляется Имя.
10 в 1:10 максимальное количество слов в строке - может быть изменено при необходимости.
Краткое решение "только встроенной функции" для решения такой проблемы, как эта, требует некоторой корректировки, так как многие попытки зашли в тупик или потребуют обходных путей из-за недостатков и недостатков встроенных формул Excel. Я предпочитаю формулы для отдельных ячеек, потому что они минимально влияют на общую структуру электронных таблиц. Тем не менее, из-за ограничений, перечисленных выше, сложные решения с одной ячейкой часто обходятся довольно длинными и громоздкими (этот ответ почему-то по-прежнему занимает всего две строки на панели формул в Excel). Я вернулся к вашему вопросу и соединил формулу, которая может (насколько я проверял) извлечь первое вхождение этого паттерна с помощью формулы из одной ячейки. Это формула массива (Ctrl+Shift+Enter вместо Enter), которая предполагает, что ваши данные находятся в A2. Эта грубая формула возвращает первые 8 символов, если совпадений не найдено, и выдает #REF, если строка короче 10 символов.
=MID(A2,MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9))),1)=" ",IF(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+9,1)=" ",IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+1,1))>64,IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+1,1))<91,IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+2,1))>64,IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+2,1))<91,IF(IFERROR(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+3,6)*1>99999,FALSE),ROW(INDIRECT("A1:A"&(LEN(A2)-9)))))))))))+1,8)
Позвольте мне попытаться сломать это по крайней мере на высоком уровне. Мы разбиваем основной текст на каждый возможный десятисимвольный блок, чтобы можно было протестировать каждый из них, используя предложение @pnuts, чтобы проверить значения Unicode для первых двух символов и запустить ISNUMBER проверьте остальную часть строки. Этот первый блок повторяется по всей моей формуле. Он генерирует список чисел от 1 до n-9, где n - длина нашей основной текстовой строки.
ROW(INDIRECT("A1:A"&(LEN(A2)-9)))
Давайте предположим, что наша строка длиной 40 символов и заменим приведенную выше формулу {1...31}, Используя это поколение числовых последовательностей, мы можем проверить, являются ли символы от 1 до 31 пробелами:
IF(MID(A2,{1...31},1)=" "
Затем мы можем проверить, являются ли символы от 10 до 40 пробелами:
IF(MID(A2,{1...31}+9,1)=" "
Затем мы можем проверить, являются ли символы от 2 до 32 заглавными буквами:
IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+1,1))>64,
IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+1,1))<91
Затем мы можем проверить, являются ли символы от 3 до 33 заглавными буквами:
IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+2,1))>64,
IF(CODE(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+2,1))<91
Затем мы можем проверить, являются ли строки символов от 4 до 9, от 5 до 10, ..., от 33 до 38, от 34 до 39 шестизначными числами:
IF(IFERROR(MID(A2,ROW(INDIRECT("A1:A"&(LEN(A2)-9)))+3,6)*1>99999,FALSE)
Если все условия имеют значение ИСТИНА, этот 10-значный тест будет возвращать индекс своего первого символа в строке через другой экземпляр исходного массива. {1...31}, В противном случае это ничего не возвращает. Мы берем Min всех возвращаемых индексов, а затем использовать Mid функция для захвата 8-значной строки, определенной вышеупомянутым минимальным индексом:
=MID(A2,MIN(matching index list)+1,8)
найдите RegExp. вы можете использовать его для извлечения числа в соответствии с шаблоном [AZ]{2}[0-9]{6}