Olá Excelmaníacos!
Hoje vi uma pergunta num fórum onde o usuário gostaria aplicar uma formatação personalizada na célula para ficar com na formatação usada em processos jurídicos, que segue este padrão: 0000000-00.0000.0.00.0000
Há um problema em tentar aplicar este formato a números no Excel, pois o Excel tem a limitação de trabalhar com até 15 dígitos quando se trata de números, fazendo com que qualquer dígito após o 15º se vire 0.
Para resolver esta situação eu visualizo duas alternativas:
1º) Compor este número em uma célula adjacente através de fórmulas com funções de texto:
Neste caso é importante que a coluna que recebe o número contínuo esteja formatada como texto para que a célula aceito os 20 dígitos. Então podemos aplicar uma fórmula usando funções como ESQUERDA, EXT.TEXTO e DIRETA para ir retirando os trechos da sequência e compondo a formatação desejada. Supondo que o número digitado está na célula A2, a fórmula pode ser:
=ESQUERDA(A2;7)&"-"&EXT.TEXTO(A2;8;2)&"."&EXT.TEXTO(A2;10;4)&"."&EXT.TEXTO(A2;14;1)&"."&EXT.TEXTO(A2;15;2)&"."&DIREITA(A2;4)
Para aperfeiçoar a fórmula, podemos criar a validação com a função SE para verificar se há 20 digitos em A2 usando também a função NÚM.CARACT, ficando:
=SE(NÚM.CARACT(A2)20;"";ESQUERDA(A2;7)&"-"&EXT.TEXTO(A2;8;2)&"."&EXT.TEXTO(A2;10;4)&"."&EXT.TEXTO(A2;14;1)&"."&EXT.TEXTO(A2;15;2)&"."&DIREITA(A2;4))
Veja o resultado na imagem:
A ventagem deste método é a facilidade de aplicar a fórmula diretamente na planilha. A desvantagem é que a formatação é aplicada na coluna auxiliar e não na coluna onde é digitado o número.
2º) Compor este número trabalhando com evento CHANGE da planilha no VBA:
Esta é uma solução mais avançada, pois envolve conhecimento em programação na linguagem Visual Basic. Para começar, também é necessário que o intervalo que receberá o número do processo esteja formatado como texto para que a célula receba o valor completo dos 20 dígitos.
Basicamente, o evento change é um gatilho que roda um determinado código cada vez que é feita uma alteração numa determinada planilha.
Para efeito deste exemplo, vamos supor que o número do processo será digitado na coluna A. Após ter formatado a coluna como texto, pressione ALT+F11 para abrir a tela do VBE.
Na tela do VBA, é preciso localizar o módulo da planilha desejada na janela do VBAProject e dar duplo clique para abrir a folha de códigos onde será inserido o código abaixo:
Private Sub Worksheet_Change(ByVal Target As Range) If Selection.Count = 1 Then Application.EnableEvents = False Dim i As Integer Dim sProcesso As String If Not Intersect(Target, Range("A:A")) Is Nothing Then If VBA.Len(Target.Value) = 20 Then Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") For i = 1 To 20 dict.Add Key:=CStr(i), Item:=VBA.Mid(Target.Value, i, 1) Next i sProcesso = _ dict("1") & dict("2") & dict("3") & dict("4") & dict("5") & dict("6") & dict("7") & "-" & _ dict("8") & dict("9") & "." & _ dict("10") & dict("11") & dict("12") & dict("13") & "." & _ dict("14") & "." & _ dict("15") & dict("16") & "." & _ dict("17") & dict("18") & dict("19") & dict("20") Target.Value = sProcesso Set dict = Nothing End If End If Application.EnableEvents = True End If End Sub
Na imagem acima o código está digitado no médulo da Plan1 que está nomeada como VBA.
Após isso, basta fechar a tela do VBE e testar digitando o processo com 20 dígitos em qualquer célula da coluna A.
A vantagem deste método é que o valor é transformado imediatamente após inserido na célula, como se fosse uma formatação. A desvantagem é que é mais complexo de aplicar, exige salvar o arquivo num formato especifico para que a macro rode, e também não é possível usar a opção desfazer ao alterar qualquer coisa na coluna A.
Conforme dito acima, é importante saber que o arquivo deverá ser salvo com a extensão .xlsm (Pasta de trabalho habilitada para macro do Excel).
Baixe aqui o arquivo com este modelo: DOWNLOAD DO ARQUIVO
OLa, tenho um layout de retorno do itau, onde eu importo o arquivo retorno e gero um padrao, ou layout, para ser importado em um sistema contabil. no fim de tudo em tenho a formula ”concatenar” para unir o padrão em a1-b1-c1-d1-e1.(são mais de 200 linhas com valores e codigos de clientes) na celula “F1″ apliquei a formula para concatenar, e apliquei no resto das celulas da coluna”F”. esse é o padrao concatenado:
030000017000000000008320000000000000000000000VALOR REFERENTE A RECEBIMENTO DE CLIENTE CONF. NF: 513242 0000169
030000018000000000195920000000000000000000000VALOR REFERENTE A RECEBIMENTO DE CLIENTE CONF. NF: 513287 0000169
030000019000000000196070000000000000000000000VALOR REFERENTE A RECEBIMENTO DE CLIENTE CONF. NF: 513288 0000169
030000020000000000006890000000000000000000000VALOR REFERENTE A RECEBIMENTO DE CLIENTE CONF. NF: 513358 0000169
eu preciso gerar o .TXT, sendo que o codigo do vba tem que iniciar na celula “F1” e vá até que não haja mais caracteres nas celulas abaixo! não pode trazer os dados ocultos da formula concatenar(espaços em branco),pois eu apliquei-a à coluna inteira, tem que trazer só o que aparece na coluna “F”.
desde já muito grato e mais ainda se puder me ajudar.
CurtirCurtir