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


В 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

4
2018-05-23 04:08


происхождения


Всегда ли строки всегда одинаковой длины? - Kevin Anthony Oppegaard Rose
@Kevin: нет, см. "U5" против "U16" - Máté Juhász
Строки имеют разную длину, и я использовал следующую формулу, которая возвращает «05» из WS-S5-S-L1-C31-F-U5-S9-P14. Но как мне вернуть «05-09-14»? = ЕСЛИ (MID (Е13, FIND ( "U" -, E13) +3,1) = "-", "0" & ​​MID (E13, FIND ( "- U", E13) +2,1), MID ( E13, FIND ( "- U", E13) +2,2)) - Indy


ответы:


Как отметил @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 (только в случаях, которые были сопоставлены на предыдущем шаге)

enter image description here

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

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

Обновить

Вы можете использовать эту формулу для вашего 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")


6
2018-05-23 08:21



Это отличная надстройка для использования. Работала отлично. Просто, наконец, я хотел бы преобразовать это в 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 - Indy
как вы имеете в виду 13 символов? Пожалуйста, обновите свой вопрос, и я обновлю свой ответ - Máté Juhász
Преобразование в основном: • 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 Таким образом, используя 7, 10, 15, 16 и 17-й символы - Indy


Довольно уродливые,
но вы можете достичь этого, следуя, предполагается, что ваши рабочие данные находятся в столбце 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")

5
2018-05-23 07:22



отличная техника, но код работает немного долго. - Indy
чтобы сделать его короче, вы можете добавить вспомогательный столбец с этой функцией: '= RIGHT (A1, LEN (A1) -FIND («U», A1,1) », а затем ссылаться на этот столбец, а не на весь этот фрагмент кода - ygaft