Преобразование штрих-кодов в определенный формат с использованием IF, FIND и MID функции Excel

Question

В Excel у меня есть ряд столбцов, содержащих символы разных типов, такие как:

WS-S5-S-L1-C31-F-U5-S9-P14 
WS-S5-S-L1-C31-F-U5-S8-P1 
WS-S5-N-L1-C29-V-U16-S6-P6 

Я хочу преобразовать их в 8 символов, используя следующие правила:

• сохранить только последние три сегмента
• удалите U и добавьте префикс 0, где это необходимо
• удалите S и добавьте префикс 0, где это необходимо
• удалите P и добавьте префикс 0, где это необходимо

Например:

• WS-S5-S-L1-C31-F-U5-S9-P14 преобразовать в 05-09-14
• WS-S5-S-L1-C31-F-U5-S8-P1 конвертировать в 05-08-01
• WS-S5-N-L1-C29-V-U16-S6-P6 преобразовать в 16-06-06

Я считаю, что есть способ использовать функцию IF , FIND & MID для преобразования их в Excel, но я не знаю, с чего начать. Любая помощь будет высоко ценится.

Обновить

Наконец, я хотел преобразовать это в 13 символов, если это возможно, например:

• WS-S5-S-L1-C31-F-U5-S9-P14 преобразовать в S1-F-05-09-14
• WS-S5-N-L2-C31-D-U5-S8-P1 преобразовать в N2-D-05-08-01
• Преобразование WS-S5-N-L1-C29-V-U16-S6-P6 в N1-V-16-06-06

Answer 1

Как отметил @ygaft, это возможно, но будет долго со стандартными функциями Excel.

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

Формула:
=RegExReplace(RegExReplace(A1,".*U([0-9]+)-S([0-9]+)-P([0-9]+)","0$1-0$2-0$3"),"0([0-9]{2})","$1")

Как это устроено:

• внутренняя функция:
  • A1: из содержимого ячейки A1
  • ".*U([0-9]+)-S([0-9]+)-P([0-9]+)" искать шаблон" ...U # -S # -P # "где" # "представляет одно или несколько чисел и запоминает числа (квадратные скобки создают группы ссылок)
  • "0$1-0$2-0$3" объединяет числа, найденные на предыдущем шаге, добавляя к ним начальный 0.
• внешняя функция:
  • RegExReplace(...) - работает с результатами внутренней функции
  • "0([0-9]{2})" - ищет 0, за которым следуют две цифры (= случаи, когда в начале 0 нет необходимости)
  • "$1" - сохраняет только две цифры, отбрасывая ведущий 0 (только в случаях, которые были сопоставлены на предыдущем шаге)

Вы также можете увидеть больше объяснений в регулярных выражениях онлайн:

• первый шаг: https://regex101.com/r/4W4dWw/1
• второй шаг: https://regex101.com/r/mA8lGs/1

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

Обновить

Вы можете использовать эту формулу для своего кода из 13 символов:
=RegExReplace(RegExReplace(A3,".*-([A-Z])-[A-Z]([0-9]).*-([A-Z])-U([0-9]+)-S([0-9]+)-P([0-9]+)","$1$2-$3-0$4-0$5-0$6"),"0([0-9]{2})","$1")

Answer 2

Довольно уродливые,
но вы можете добиться этого следующим образом: предполагается, что ваши рабочие данные находятся в столбце A:

=TEXT(LEFT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-1),"00")&"-"&TEXT(MID(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),FIND("-",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)+2,(FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)-2-FIND("S",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1))),"00")&"-"&TEXT(RIGHT(RIGHT(A1,LEN(A1)-FIND("U",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND("U",A1,1)))-FIND("P",RIGHT(A1,LEN(A1)-FIND("U",A1,1)),1)),"00")