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

Question

Então, aqui está o que eu estou procurando fazer. Eu tenho uma planilha com uma célula que tem uma seqüência de números (alguns são mais longos que outros) Eu preciso extrair cada oito números e colocá-los em linhas únicas.

Aqui está 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

Isso é o que eu 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 gerados vão até 30 ou mais grupos de 8 em uma linha. Com mais de 1000 linhas de dados para separar. Existe algum tipo de código que ajudará a tornar este processo menos assustador? Atualmente estou usando Left, mid e right, mas isso vai me levar anos para terminar. As planilhas estão saindo do acesso, por isso, se houver uma maneira mais fácil de concluí-la, estou aberto a sugestões. O produto final deve ser salvo em um csv. Também preciso manter os 8 dígitos inteiros no produto concluído antes de salvá-lo como um csv, para que eu possa criar o link para onde essa imagem está localizada. Obrigado pela ajuda!

    

Answer 1

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

1. Crie uma tabela que não seja nada além de uma lista de números: 1, 2, 3, 4, ... n , em que n é o maior número de itens que já estariam no seu IMAGELIST . Não é um custo muito alto para torná-lo maior do que o necessário, portanto, suba para 50 ou algo assim.
2. Crie uma consulta que atraia sua tabela original e a tabela de números sem qualquer junção . Se você acabou de fazer SELECT * , você receberá n de cópias de cada registro em sua tabela original.
3. Em vez de inserir o campo IMAGELIST , adicione uma fórmula que execute a função MID no campo IMAGELIST com base no valor da tabela de números.
4. Filtrar para mostrar apenas os registros em que a fórmula não retorna uma string em branco. Isso significa que um registro com IMAGELIST 24 caracteres só aparece 3 vezes em vez de n vezes com um monte de 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;

No meu modelo rápido, Table1 é sua tabela original, Table2 é a tabela de números, Number é o único campo em Table2 e são apenas os números 1-n e eu adicionei o campo de autonumeração ID to Table1 apenas para classificação.

Isso tudo pressupõe que cada item em IMAGELIST tenha exatamente 8 caracteres. Se isso variar de tamanho, essa abordagem não funcionará.

    

Answer 2

Isso não é muito difícil de fazer com funções de planilha do Excel. Suponhamos que seus dados estejam em Sheet1 com 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 (por exemplo, através da coluna E , com base no seu exemplo de cinco colunas),
  • ou simplesmente copie os cabeçalhos de Sheet1 para Sheet2 .
• A2 → =OFFSET(Sheet1!A$1, $F2-1, 0)
  • e arraste para a direita para abranger todas as colunas , exceto para IMAGELIST (isto é, através da coluna D ).
  • Em algum momento, você desejará formatar as colunas Sheet2 corretamente (isto é, aqueles que contêm datas).
• E2 → =LEFT($G2, 8)
• F2 → 2
• G2 → =OFFSET(Sheet1!E$1, $F2-1, 0)
  • (é o mesmo que as fórmulas em Colunas A - D , exceto referenciando Sheet1 , coluna E .)
• H2 → =RIGHT($G2, LEN($G2)-8)

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

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

Agora arraste / preencha a linha 3 para baixo para quantas linhas precisar para obter todos os dados de Sheet1 :

Copie e cole valores & formatos.

Explicação:

• Coluna F indica qual linha na coluna Sheet1 this (em Sheet2 ) está puxando dados de.
• Sempre que avançamos para uma nova linha Sheet1 (ou seja, cada vez que a coluna F aumenta), A coluna G obtém um valor completo, 8 × n -digit-long IMAGELIST de Sheet1 .
• O valor na coluna G é dividido: primeiros oito dígitos na coluna E e o restante na coluna H .
• Contanto que tenhamos um restante não vazio na Coluna H , copiamos para a coluna G e deixamos a coluna F igual (isto é, acessando a mesma linha em Sheet1 ).
• Quando ficamos sem dados na coluna H , isso significa que decompomos um valor 8 × n -digit-long IMAGELIST de Sheet1 para as linhas n em Sheet2 , Assim, incrementamos a coluna F e buscamos o próximo valor IMAGELIST .

Answer 3

Existem dois passos que você precisa fazer para atingir sua meta:

1 divide 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 2º passo é desmembrar seus dados, infelizmente não é simples, mas existem muitas soluções na internet, como esta: É possível" unpivot "ou" pivô reverso "no Excel?