Как "найти и заменить по ВПР" по списку, часть текста

Question

Список файлов для переименования
Список старых названий
и параллельно ему список новых названий

Надо заменить старые названия на новые, сохранив при этом формат фалов suffix prefix.

Думаю надо найти "Старое название" в "Списке файлов" и заменить на "Новое название" по ВПР

Что пребывал:

Удалить формат suffix prefix , потом по ВПР подобрать замену, но потом не понятно как вернуть такой же формат suffix prefix

=ARRAYFORMULA(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(C2:C;".JPG";"");".PDF";"");".AI";"");".SLDDRW";"");".SLDPRT";"");".jpg";"");".png";""))

2.Разделить по точке "." не получится так как в названии есть точки, да и проблемы с suffix/prefix

Пример таблицы

Уточнение 1: в некоторых названиях может быть несколько "." и "_"

Уточнение 2: строк примерно 800

Answer 1

Из-за названия, в котором нет нижнего подчеркивания, приходится обходить ошибку и рисовать формулу в два раза длиннее:

=ЕСЛИОШИБКА(                                  
ВПР(ЛЕВБ(A3;ПОИСК("_";A3)-1);$B$3:$C$20;2;0)&ПСТР(A3;ПОИСК("_";A3);99);                                 
ВПР(ЛЕВБ(A3;ПОИСК(".";A3)-1);$B$3:$C$20;2;0)&ПСТР(A3;ПОИСК(".";A3);99))

Answer 2

Предположим, что нужно заменить не "часть теста", а левую часть имени файла. Тогда справедливо:

для замены всех, ненайденные переименовываются сами в себя:

=INDEX(IFERROR(VLOOKUP(
  ROW(A2:A25);
  SPLIT(FLATTEN(IF(
    REGEXMATCH(A2:A25;TRANSPOSE(B2:B25));
    ROW(A2:A25) & "❤" & REGEXREPLACE(A2:A25;TRANSPOSE(B2:B25);TRANSPOSE(C2:C25));
  ));"❤");
  2;
);A2:A25))

для замены всех, ненайденные возвращают пустоту:

=INDEX(IFERROR(VLOOKUP(
  ROW(A2:A25);
  SPLIT(FLATTEN(IF(
    REGEXMATCH(A2:A25;TRANSPOSE(B2:B25));
    ROW(A2:A25) & "❤" & REGEXREPLACE(A2:A25;TRANSPOSE(B2:B25);TRANSPOSE(C2:C25));
  ));"❤");
  2;
);))

Следует обратить внимание, что сортировка диапазона B2:C25 может повлиять на результат работы формулы.

Для более сложной однопартийной замены нужно усложнять формулу REGEXREPLACE(A2:A25;TRANSPOSE(B2:B25);TRANSPOSE(C2:C25))

Пример в Таблице https://docs.google.com/spreadsheets/d/1cfF88hVRfMumLDPXdUU-R4dr3oDycDrBlSoCFLgy148/edit?usp=sharing

БЛОГ НА HUSL

Как "найти и заменить по ВПР" по списку, часть текста

Ответы (2 шт):