Questão Convertendo códigos de barras para um formato específico usando a função IF, FIND & MID do excel


No Excel eu tenho um número de colunas contendo caracteres de diferentes tipos, tais como:

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 

Eu quero converter estes para 8 caracteres usando as seguintes regras:

  • manter apenas os últimos três segmentos
  • remova o U e adicione o prefixo 0 quando apropriado
  • remova S e adicione o prefixo 0 quando apropriado
  • remova P e adicione o prefixo 0 quando apropriado

Por exemplo:

  • WS-S5-S-L1-C31-F-U5-S9-P14 converter para 05-09-14
  • WS-S5-S-L1-C31-F-U5-S8-P1 converter para 05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 converter para 16-06-06

Eu acredito que há uma maneira de usar IF, FIND E MID função para convertê-los no Excel, mas não sabe como começar. Qualquer ajuda será muito apreciada.

Atualizar

Por fim, queria converter isso em 13 caracteres, se possível, por exemplo:

  • WS-S5-S-L1-C31-F-U5-S9-P14 converter para S1-F-05-09-14
  • Conversor WS-S5-N-L2-C31-D-U5-S8-P1 para N2-D-05-08-01
  • WS-S5-N-L1-C29-V-U16-S6-P6 convertem para N1-V-16-06-06

4


origem


As cordas estão sempre do mesmo tamanho? - Kevin Anthony Oppegaard Rose
@Kevin: não, veja "U5" vs "U16" - Máté Juhász
Cordas são de comprimento diferente e eu usei a seguinte fórmula que retorna "05" de WS-S5-S-L1-C31-F-U5-S9-P14. Mas como eu volto "05-09-14"? = IF (MID (E13, ENCONTRAR ("- U", E13) +3,1) = "-", "0" & ​​MID (E13, ENCONTRAR ("- U", E13) +2,1), MID ( E13, FIND ("- U", E13) +2,2)) - Indy


Respostas:


Como @ygaft apontou, é possível, mas será longo com as funções padrão do Excel.

Eu uso livre RegEx encontrar / substituir add-in em situação como essa, usando uma expressão regular, você pode conseguir mais fácil.

A fórmula:
=RegExReplace(RegExReplace(A1,".*U([0-9]+)-S([0-9]+)-P([0-9]+)","0$1-0$2-0$3"),"0([0-9]{2})","$1")

Como funciona:

  • função interna:
    • A1: do conteúdo da célula A1
    • ".*U([0-9]+)-S([0-9]+)-P([0-9]+)" procure por um padrão "... U # -S # -P #" onde "#" representa um ou mais números e se lembra dos números (colchetes criam grupos de referência)
    • "0$1-0$2-0$3" mescla os números encontrados na etapa anterior, adicionando 0 à frente a todos eles.
  • função externa:
    • RegExReplace(...) - trabalha com resultados da função interna
    • "0([0-9]{2})" - procura 0 seguido por dois dígitos (= casos em que 0 não é necessário)
    • "$1" - mantém apenas os dois dígitos, largando 0 à esquerda (somente nos casos que foram correspondidos na etapa anterior)

enter image description here

Você também pode ver mais explicações sobre as expressões regulares on-line:

Nota: Eu não sou afiliado de forma alguma com esse add-in, apenas use-o, pois facilita minha vida.

Atualizar

Você pode usar essa fórmula para o seu código de 13 caracteres:
=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



Esse é um ótimo complemento para usar. Trabalhou perfeitamente. Por fim, eu queria converter isso em 13 caracteres, se possível, por exemplo: * WS-S5-S-L1-C31-F-U5-S9-P14 convertido para S1-F-05-09-14 * WS-S5- Conversor de N-L2-C31-D-U5-S8-P1 para N2-D-05-08-01 * WS-S5-N-L1-C29-V-U16-S6-P6 convertido em N1-V-16- 06-06 - Indy
como você quer dizer 13 caracteres? Por favor, atualize sua pergunta e eu atualizarei minha resposta - Máté Juhász
Basicamente converter: • WS-S5-S-L1-C31-F-U5-S9-P14 para S1-F-05-09-14 • WS-S5-N-L2-C31-D-U5-S8-P1 para N2-D-05-08-01 • WS-S5-N-L1-C29-V-U16-S6-P6 a N1-V-16-06-06 Então, usando o 7º, 10º, 15,16 e 17º caractere - Indy


Bonito feio,
mas você pode alcançá-lo da seguinte maneira, assume que seus dados de trabalho estão na coluna 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



grande técnica usada, mas o código é um pouco longo. - Indy
para torná-lo mais curto, você pode adicionar coluna auxiliar com essa função: '= RIGHT (A1, LEN (A1) -FIND ("U", A1,1) "e, em seguida, referem-se a essa coluna em vez de todo esse pedaço de código - ygaft