☰

Como faço para dividir um número em uma única célula que não possui vírgulas e colocá-lo em várias linhas?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-access
Como faço para dividir um número em uma única célula que não possui vírgulas e colocá-lo em várias linhas?

Então aqui está o que estou procurando fazer. Eu tenho uma planilha com uma célula que contém uma sequência de números (alguns são mais longos que outros). Preciso extrair a cada oito números e colocá-los em linhas únicas.

Esta é a aparência da planilha atual:

IFIELD1     IFIELD2   IFIELD3     IFIELD4    IMAGELIST
05/30/2009  ERMS      100 Day     L. C.      0000001200000013
05/30/2009  ESE       20 Day      J. R.      0000001400000015
05/30/2009  IHS       100 Day     H. W.      00000016
05/30/2009  LMS       100 Day     D. E.      0000001700000018

Isto é o que estou procurando:

IFIELD1     IFIELD2   IFIELD3   IFIELD4   IMAGELIST
05/30/2009  ERMS    100 Day     L. C.     00000012
05/30/2009  ERMS    100 Day     L. C.     00000013
05/30/2009  ESE     20 Day      J. R.     00000014
05/30/2009  ESE     20 Day      J. R.     00000015
05/30/2009  IHS     100 Day     H. W.     00000016
05/30/2009  LMS     100 Day     D. E.     00000017
05/30/2009  LMS     100 Day     D. E.     00000018

Alguns números de partida chegam a 30 ou mais grupos de 8 em uma linha. Com mais de 1.000 linhas de dados para separar. Existe algum tipo de código que ajudará a tornar esse processo menos assustador? Atualmente estou usando Esquerda, meio e direita, mas vou levar anos para terminar. As planilhas estão saindo do acesso então se houver uma maneira mais fácil de preenchê-las aí estou aberto a sugestões. O produto final deve ser salvo em csv. Além disso, preciso manter todos os 8 dígitos do produto concluído antes de salvá-lo como um csv para poder criar o link para onde a imagem está localizada. Obrigado pela ajuda!

Responder1

Existe uma solução bastante fácil no Access.

  1. Crie uma tabela que nada mais seja do que uma lista de números: 1, 2, 3, 4, ... nonde nestá o maior número de itens que existiriam no seu arquivo IMAGELIST. Porém, não é um custo muito alto torná-lo maior do que o necessário, então vá até 50 ou algo assim.
  2. Crie uma consulta que extraia sua tabela original e a tabela de númerossem nenhuma junção. Se você fizesse isso SELECT *neste momento, obteria ncópias de todos os registros da sua tabela original.
  3. Em vez de puxar o IMAGELISTcampo, adicione uma fórmula que execute a MIDfunção no IMAGELISTcampo com base no valor da sua tabela numérica.
  4. Filtre para mostrar apenas os registros onde a fórmula não retorna uma string em branco. Isso significa que um registro com IMAGELIST24 caracteres aparecerá apenas 3 vezes, em vez de nvezes com vários registros em branco.

Aqui está o SQL para essa consulta:

SELECT Table1.ID, Table1.IFIELD1, Table1.IFIELD2, Table1.IFIELD3, Table1.IFIELD4, Table2.Number, Mid([IMAGELIST],([Number]-1)*8+1,8) AS ImageListParse
FROM Table1, Table2
WHERE Mid([IMAGELIST],([Number]-1)*8+1,8)<>""
ORDER BY Table1.ID, Table2.Number;

Na minha maquete rápida, Table1é a sua tabela original, Table2é a tabela de números, Numberé o único campo Table2e são apenas os números 1-n, e adicionei o campo de numeração automática IDapenas Table1para classificação.

Tudo isso pressupõe que cada item IMAGELISTtenha exatamente 8 caracteres. Se variar em comprimento, essa abordagem não funcionará.

Responder2

Isso não é muito difícil de fazer com funções de planilha do Excel. Vamos supor que seus dados estejam Sheet1com cabeçalhos na linha 1. Crie Sheet2. Preencha-o da seguinte forma:

  • A1→=Sheet1!A1
    • e arraste para a direita para cobrir todas as colunas (ou seja, através de Column  E, com base no seu exemplo de cinco colunas),
    • ou simplesmente copie os cabeçalhos de Sheet1para Sheet2.
  • A2→=OFFSET(Sheet1!A$1, $F2-1, 0)
    • e arraste para a direita para cobrir todas as colunasexceto por IMAGELIST (ou seja, através de Column  D).
    • Em algum momento você desejará formatar as Sheet2colunas corretamente (ou seja, aquelas que contêm datas).
  • E2→=LEFT($G2, 8)
  • F2→2
  • G2→=OFFSET(Sheet1!E$1, $F2-1, 0)
    • (Isso é igual às fórmulas em Columns  A- D, exceto referenciando Sheet1, Column  E.)
  • H2→=RIGHT($G2, LEN($G2)-8)

Arraste/preencha a linha 2 até a linha 3. Em seguida, altere

  • F3para =IF(H2<>"", F2, F2+1)e
  • G3para=IF(H2<>"", H2, OFFSET(Sheet1!E$1, $F3-1, 0))

Agora arraste/preencha a linha 3 para baixo em quantas linhas forem necessárias para obter todos os dados de Sheet1:

Em seguida, copie e cole valores e formatos.

Explicação:

  • A coluna  Findica em qual linhaSheet1 esserow (on Sheet2) está extraindo dados.
  • Cada vez que avançamos para uma nova Sheet1linha (ou seja, cada vez que a Coluna  Faumenta), a Coluna  Gobtém um resultado completo de 8×n-valor de dígito longo IMAGELISTde Sheet1.
  • O valor em Column  Gé dividido: os primeiros oito dígitos em Column  Ee o restante em Column  H.
  • Contanto que tenhamos um resto não vazio em Column  H, nós o copiamos para Column  Ge deixamos Column  Figual (ou seja, acessando a mesma linha em Sheet1).
  • Quando ficamos sem dados em Column  H, isso significa que decompomos um 8×nIMAGELIST-valor longo de dígitos de Sheet1aténlinhas em Sheet2, então incrementamos Column  Fe buscamos o próximo IMAGELISTvalor.

Responder3

Existem duas etapas que você precisa seguir para atingir seu objetivo:

1 divida os números em colunas diferentes, por exemplo, no Excel 2010: Dados - texto para colunas - largura fixa - defina a largura como 8 e clique em Concluir.

2 O segundo passo é desdinamizar seus dados, infelizmente não é simples, mas existem muitas soluções na internet, como esta:É possível "desdinamizar" ou "reverter o pivô" no Excel?

informação relacionada