como posso dividir os dados em várias colunas separando somente após os primeiros dois pontos no excel

Question

Eu tenho uma enorme lista de excel (20k + linhas) e quero dividir essa lista de uma coluna em várias colunas. Anteriormente, eu usava o Assistente de Dados | Colunas de Texto para Colunas, mas parece ter uma limitação: Não é possível especificar após quantos "divisões" parar para dividir. Exemplo de dados:

type: abds : das
files: asdfs:jgkd

o objetivo é:

|type|abds : das|
|files|asdfs:jgkd|

obrigado pelo seu tempo:)!

    

Answer 1

VBA é uma excelente ferramenta para pequenas tarefas como esta.

Supondo que os dados estão na coluna A e são contíguos em A1 . Se não for esse o caso, basta ajustar a linha Set rng = ... para se adequar aos seus dados.

Sub SplitColumn()
    Dim rng As Range
    Dim dat As Variant
    Dim i As Long, j As Long

    Set rng = Range([a1], [a1].End(xlDown)).Resize(, 2)
    dat = rng
    For i = 1 To UBound(dat, 1)
        j = InStr(dat(i, 1), ":")
        If j > 0 Then
            dat(i, 2) = Mid$(dat(i, 1), j + 1)
            dat(i, 1) = Left$(dat(i, 1), j - 1)
        End If
    Next
    rng = dat
End Sub

    

Answer 2

Normalmente, você escreveria algum vbscript para executar essa tarefa, mas se for uma planilha de uma coluna, você poderá salvá-lo como Texto (delimitado por tabulação) e abri-lo novamente. O Excel perguntará que tipo de arquivo de texto é. Diga que é um file with Separators , clique em Next e marque Other e digite dois pontos no campo de edição.

    

Answer 3

Você pode substituir o primeiro cólon por um "|" - ou algum outro caracter que não apareça nos seus dados - usando:

=SUBSTITUTE(A1,":","|",1)

Copie e cole os resultados como valores e faça um Text To Columns nesses valores.

    

Answer 4

A maneira mais simples de pensar é começar com o que você já fez: o Assistente de Dados | Text-to-Columns.

Você então concatena os campos que foram divididos sem intenção.