Texto em colunas no Excel com VBA

Lista com Filtro de Dados Automático 2
23 de setembro de 2010
Função para extrair números de células
27 de setembro de 2010

Objetivo: Este artigo apresenta a criação de uma macro para separar arquivos texto no Excel em colunas automaticamente.

É extremamente útil para quem tem que abrir relatórios em TXT ou CSV ou formato que o valha e diariamente tenha que realizar a sua separação em colunas, como por exemplo em relatórios de vendas, relatórios contábeis, arquivos bancários como extratos e recebimentos.

Você pode acompanhar pelo vídeo abaixo ou pelo tutorial escrito como preferir.

Para seguir este passo-a-passo realize o download do arquivo texto:

  1. Abra o Excel
  2. No Excel, clique em abrir e altere o campo “Arquivos do tipo” para “Todos os arquivos (*.*)“, localize desta forma o arquivo texto que você realizou o download
  3. Selecione o arquivo texto, ainda sem clicar em abrir
  4. Com o arquivo selecionado pressione CTRL+SHIFT+ENTER, este comando fará com que o arquivo seja aberto na íntegra em uma só coluna.

Criando a Macro

  • Clique na guia Desenvolvedor
  • Clique sobre o botão Gravar Macro
  • Digite um nome para a Macro e digite uma tecla de atalho, sendo que esta tecla de preferência não deve ser a de nenhum atalho do Excel, por isso dê preferência a utilizar letras maiúsculas, bastando para isso digitar Shift+ a letra, no caso utilizando a letra “O”.
  • Em Armazenar macro em: selecione “Pasta de trabalho pessoal de macros”, para que fique disponível em todo o sistema
  • Em Descriçãodigite uma descrição sobre a macro

  • Clique em OK. Atenção, a partir deste momento o Excel estará gravando todas as suas ações.
  • Selecione a coluna A
  • Clique na Guia Dados
  • Clique no botão Texto para Colunas. O sistema irá abrir o Assistente para conversão de texto em colunas
  • Clique em Delimitado e no botão Avançar

  • Na tela que segue você determina o delimitador das tabelas, que neste caso será “Ponto e vírgula” , sendo que você pode selecionar qualquer delimitador, ou ainda na tela anterior selecionar Largura fixa e determinar aonde o arquivo texto será cortado para criar as colunas.
  • Após determinar o delimitador “;”  você notará linhas verticais delimitando as colunas
  • Clique em Avançar
  • Na próxima tela você poderá determinar os tipos de dados de cada uma das colunas, no nosso caso vamos deixar como está.
  • Clique em Concluir.
  • Selecione toda a planilha e clique entre duas colunas para que o Excel realize o ajuste automático de todas as colunas.
  • Clique na célula A1.
  • Clique na guia Desenvolvedor.
  • Clique no botão Parar Gravação.

Pronto! A sua macro para abrir este tipo de arquivo texto estará pronta, sendo que para testar você deve fechar o arquivo texto sem Salvar o arquivo texto, porém salvando a Macro criada caso o Excel questione.

Depois abra o arquivo texto seguindo os mesmos passos do início do post, selecionando o arquivo e pressionando as teclas CTRL+SHIFT+ENTER.

Após abrir o arquivo execute a macro, pressionando a tecla de atalho que você determinou na gravação, no nosso caso seria CTRL+SHIFT+o.

O seu arquivo será separado em colunas automaticamente dentro do padrão determinado.

Dica: Você pode melhorar este exemplo realizando formatações diretamente sobre as colunas criadas, adicionando uma linha no início e colocando cabeçalhos, caso o seu arquivo não tenha, colocando negrito, etc. [saiba_mais]


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

10 Comentários

  1. Felipe Tadeu disse:

    Boa noite!

    Trabalhar com arquivos txt, é muito importante principalmente para quem trabalha com extratos bancários, relatórios contabeis precisa fazer manipulações de dados e fazer relatórios gerenciais.
    Uso muito txt, sou analista finaceiro para elaborar relatórios pois sistema da empresa gera relatórios em txt.
    Sou especialista Excel VBA, desenvolve varias tecnicas para uso desses arquivos até mesmo como banco de dados quem tiver interesse me avise!

    Abraços,

    Felipe

    • Ottavio disse:

      Opa Felipe tudo certo? Preciso criar uma tabela a partir de um texto separado por virgulas, e sempre com o mesmo padrão.

      Muito dificil? tem alguma ideia ? Sou novo em VBA e não consigo achar uma opção…

      Obrigado pela atenção,

      Att, Ottavio de Lucca

  2. Felipe Tadeu disse:

    Esse codigo por Exemplo cria um arquivo txt, de nome teste.
    Dim FileNum
    ‘Obtém um número de arquivo livre
    FileNum = FreeFile
    ‘Abre o arquivo Teste.txt para a saída (ou seja cria)
    Open ThisWorkbook.Path & “\Teste.txt” For Random As FileNum

    Criado: Felipe Tadeu

  3. Danillo disse:

    Não seria mais facio se pegar este aquivo caso seja “.txt” salvar ele com a extensão “.csv” e abrir este arquivo no excel???

    • Marcos Rieper disse:

      Bom dia Danillo,

      É que muitas vezes este arquivo está formatado em txt, separado ou não por , ou ; no caso de csv.

      Ás vezes é um arquivo delimitado conforme um mapa fornecido por uma empresa.

      Abraço

      Marcos Rieper

  4. Daniel disse:

    Como faço para multiplicar combinações d células d determinada coluna.coluna A,B,C_2,4,6. ai dá total d linha?

    NA COLUNA “A” TENHO 3 LINHAS(A2,A3,A4)
    NA COLUNA “B” TENHO 5 LINHAS(B2,B3,B4,B5,B6)
    NA COLUNA “C” TENHO 2 LINHAS(C2,C3)
    AI DÁ UM TOTAL DE 3X5=15X2=30LINHAS

    AS 30 LINHAS QUERO EM OUTRA PLANILHA, SERIA BEM UMA COMBINAÇÃO DE CÉLULAS SÓ COM OS RESULTADO EM OUTRA PLANILHA.
    NÃO TENHO CERTEZA MAIS ACHO Q SÓ PODE SER FEITO PELO VBA.
    IMAGINE UMA LISTA DE 5MIL E você FAZER MANUAL.FICA difícil.
    AGRADEÇO SE ALGUÉM PUDER ME AJUDAR.VALEU

  5. David Kraut disse:

    Tem como eu usar o Texto para Colunas na Macro para formatar o txt?

    Pois tenho um relatorio em txt que na coluna de valores vem . no lugar de , e ao exportar o Excel não considera como numero ai para arrumar tenho que usar o texto para coluna. Da pra usar esta função em uma Macro. Para na hora que eu usar esta Macro que ja formata esta planilha ela tbm ja utilizar o texto para colunas?

    Obrigado

  6. SAMUEL MARTINS disse:

    E o inverso?
    PReciso de um arquivo em *.txt formatado para importar em um sistema. Já ajustei as larguras das colunas e tal. Porém quando abro o arquivo salvo ele não obecedeu as posições das colunas (largura predefinida).

    Exemplo:
    Coluna A possui 10 Posições (inicia em 1 até 10)
    Coluna B possui 5 posições (inicia em 11 até 15)
    Coluna C possui 10 posições (inicia em 16 até 25)

    Alguém sugere uma macro para isso?

  7. Emerson disse:

    Pessoal, tenho um arquivo txt gerado no padrão abaixo, gostaria de jogar os campos (ONU No. 1…X ) e (-21.64 valores dBm) em colunas do excel, ou seja preciso de dua colunas. As demais informações serão descartadas. Alguém tem uma ideia de como isso poderia ser feito? Desde já obrigado.

    ********************************************************************************
    OptModule Para Information
    ********************************************************************************
    OLT No. 2
    PON No. 1
    Optical Module Type(KM) 20
    Temperature(¡æ) 38.89
    Voltage(V) 3.24
    Bias Current(mA) 5.61
    Tx_power(dBm) 4.69
    ———————————-
    ONU No. 1
    Receive OptPower(dBm) -21.64
    1 ——————————
    ONU No. 2
    Receive OptPower(dBm) -22.68
    2 ——————————
    ONU No. 3
    Receive OptPower(dBm) -22.31
    3 ——————————
    ONU No. 4
    Receive OptPower(dBm) -20.27
    4 ——————————

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.