Número por Extenso no Excel com Fórmula

Leia neste artigo como escrever número por extenso no Excel com fórmula automaticamente e sem VBA automaticamente.

Assista a vídeo-aula passo-a-passo de como funciona e como desenvolver abaixo. Inscreva-se no canal do Fábio Gatti para mais aulas como esta.

Contextualização Número por Extenso Excel

Por quantas vezes já precisamos de soluções específicas no Excel, e que a única saída que restava era o bom (nem tanto…) e velho VBA?

Esse é um caso! Você já precisou escrever o número em uma célula do Excel e aparecesse seu valor por extenso em uma célula ao lado?

Isso faz com que usuários tenham de copiar soluções prontas em VBA para suas planilhas, ou até baixar extensões e suplementos para realizar tais ações.

Bom… como diriam as organizações Tabajara: SEUS PROBLEMAS ACABARAM!

Com o nosso excelente Microsoft 365 e o advento das matrizes dinâmicas, junto das poderosíssimas funções LAMBDA e LET, consegui criar essa funcionalidade NATIVAMENTE, apenas com uso de fórmulas e funções.

Esse foi um dos meus temas de palestra no MVP Conf do ano passado (2021), e estou abrindo agora com todos vocês a solução, para que possam reutilizá-la em seus trabalhos.

A agenda e os conteúdos fornecidos no MVP Conf podem ser acessados em www.mvpconf.com.br

Documentação

Necessário: Microsoft 365 devidamente atualizado.

Não é necessário inscrição no programa Insider.

Me baseei para estudo na rotina que encontrei no blog do Macoratti:

https://www.macoratti.net/14/04/vba_ext1.htm

A rotina basicamente segue o seguinte fluxo de ações:

Criando a Solução de Número por Extenso no Excel

A organização foi feita da seguinte forma: inseri um valor aleatório com 2 decimais em uma célula, e fui destrinchando cada etapa, para depois uni-las em uma única função.

Consideraremos então, a célula inicial como sendo B1

Como o Excel possui um limite de 15 dígitos de valor numérico, eu limitei (assim como a macro) para, no máximo, 999.999.999.999 (vulgo “quase um trilhão” … já está bom, né?)

Então, para o primeiro cálculo:

Célula A4: =B1>999999999999999

Que retornará VERDADEIRO ou FALSO, que, caso positivo, receberá um tratamento posterior de “Número excede o limite permitido”.

Para a sequência, vamos criar algumas “fatias” do número digitado, extraindo a parte inteira e decimal em duas células:

Célula B4: =INT(B1)

Célula C4: =ARRED(B1-B4;2)*100

Consequentemente, já vou aproveitar esse VERDADEIRO e FALSO retornado para retornar o texto monetário:

Célula C4: =SES(B4=0;””;B4=1;” real”;1;” reais”)

Agora entra uma parte que eu considero que foi a principal “sacada” (o “Pulo do Gatti”, se me permitem o trocadilho) da construção da fórmula… Todo número, quando escrito por extenso, tem a escrita variando entre unidades, dezenas e centenas… por exemplo, o valor 1.001.000 é lido como “um milhão e um mil reais”, o milhão e o mil são acrescidos após as unidades, dezenas e centenas terem sido escritas.

Para isso, criei uma fórmula para gerar um “delimitador” entre os grupos de centenas possíveis, usando a própria formatação de número para me auxiliar:

Célula E4: =TEXTO(B4;”#.0″)

Dessa forma, o número que foi colocado no exemplo, passou a ser visto como 2.419.491, forçando os pontos a aparecerem como texto.

Estamos então, até o momento, com o seguinte:

Número por Extenso no Excel com Fórmula 3

Preciso agora “quebrar” esse valor em grupos de centenas… no exemplo em questão, teríamos 3 grupos numéricos de: 2, 419 e 491, respectivamente.

Para os inscritos no Microsoft Insider, já existe a função que faz essa quebra (TEXTODIVISÃO)… porém, até a data atual, como não existe essa função, vamos fazer a boa e velha gambiar… digo, “recurso técnico alternativo”.

A função FILTROXML permite que façamos quebras de strings baseado em tags… ou seja: preciso apenas considerar que o “.” é a quebra de linha entre os números.

A célula F4, nesse exemplo, retorna 2.419.491… vou transformar esse número em:

<t><r>2</r><r>419</r><r>491</r></t>…

Número por Extenso no Excel com Fórmula 4

seria algo do tipo:

A fórmula ficaria, então:

Célula G4:

=FILTROXML(“<t><r>”&SUBSTITUIR(F4;”.”;”</r><r>”)&”</r></t>”;”//r”)

O resultado será uma matriz dinâmica, separando cada conjunto de centena + dezena + unidade em uma célula.

Posteriormente terei de usar um número de apoio… então já vou fazê-lo, que será de contar quantos itens há nessa matriz gerada.

Célula H4: =CONT.VALORES(G4#)

Agora, como já tenho as matrizes separadas, cada uma delas terá de ser desmembrada entre as três possibilidades: centena, dezena e unidade, e cada uma das possibilidades será convertida para um texto individual.

Como apoio, criei 4 matrizes:

Número por Extenso no Excel com Fórmula 5

Essas matrizes servirão como base de consulta para cada número dentro dos grupos respectivos.

Agora, vamos extrair cada item na sua respectiva parte…

Para Unidades: (Célula Q4) =–DIREITA(G4#)

Para Dezenas: (Célula R4) =INT(DIREITA(G4#;2)/10)

Para Centenas: (Célula S4) =INT(G4#/100)

Vamos criar as fórmulas agora para validar se cada uma das 4 matrizes será usada (Unidades, Dezenas (Dez e Dezena, conforme imagem anterior) e Centenas.

Apesar de podermos usar as funções E e OU, como faremos uso posteriormente dessas fórmulas em matrizes dinâmicas, vamos evitar o uso dessas funções auxiliares…

Fazer Centena: (Célula T4) =S4#>0

Fazer Dezena: (Célula U4) =(NÃO(V4#)*(R4#>0))=1

Fazer Dez: (Célula V4) =((R4#=1)*(Q4#>0))=1

Fazer Unidade: (Célula W4) =(NÃO(V4#)*(Q4#>0))=1

Ao fazermos essas separações, temos então as extrações separadas de cada uma parte dos grupos gerados:

Extração Centena: (Célula X4) =SE(T4#;PROCV(S4#;O4:P12;2;0);””)

Extração Dezena: (Célula Y4) =SE(U4#;PROCV(R4#;M4:N12;2;0);””)

Extração Dez: (Célula Z4) =SE(V4#;PROCV(Q4#;K4:L12;2;0);””)

Extração Unidade: (Célula AA4) =SE(W4#;PROCV(Q4#;I4:J12;2;0);””)

Por fim desta parte, vamos unificar todos os textos gerados pelas “partes”, fazendo uso da excelente função UNIRTEXTO… PORÉM, entretanto, todavia, … Não posso simplesmente pedir para unificar toda a matriz, pois são 4 colunas distintas que estamos fazendo por enquanto.

Ou seja: vou pegar “linha a linha”, começando pela 4, e arrastando para baixo

Célula AB4 até AB6 (arrastar) =UNIRTEXTO(” e “;VERDADEIRO;X4:AA4)

Quando unificar na função LET vai simplificar isso…

Para termos algo semipronto, contendo a matriz dos valores por extenso, sem adicionar a ordem de grandeza, a fórmula fica, até o momento, assim (na célula AD4)

=LAMBDA(Valor;
     LET(
          Centena;INT(Valor/100);
          Dezena;INT(DIREITA(Valor;2)/10);
          Unidade;--DIREITA(Valor);

          FazerCentena;Centena>0;
          FazerDez;((Dezena=1)*(Unidade>0))=1;
          FazerDezena;(NÃO(FazerDez)*(Dezena>0))=1;
          FazerUnidade;(NÃO(FazerDez)*(Unidade>0))=1;

          MatrizUnidades;{1\"um";2\"dois";3\"três";4\"quatro";5\"cinco";6\"seis";7\"sete";8\"oito";9\"nove"};
          MatrizDez;{1\"onze";2\"doze";3\"treze";4\"quatroze";5\"quinze";6\"dezesseis";7\"dezessete";8\"dezoito";9\"dezenove"};
          MatrizDezena;{1\"dez";2\"vinte";3\"trinta";4\"quarenta";5\"cinquenta";6\"sessenta";7\"setenta";8\"oitenta";9\"noventa"};
          MatrizCentena;{1\"cento";2\"duzentos";3\"trezentos";4\"quatrocentos";5\"quinhentos";6\"seiscentos";7\"setecentos";8\"oitocentos";9\"novecentos"};

          ExtensoCentena;SE(FazerCentena;SE(((Unidade=0)*(Dezena=0)*(Centena=1))=1;"cem";PROCV(Centena;MatrizCentena;2;0));" ");
          ExtensoDezena;SE(FazerDezena;PROCV(Dezena;MatrizDezena;2;0);" ");
          ExtensoDez;SE(FazerDez;PROCV(Unidade;MatrizDez;2;0);" ");
          ExtensoUnidade;SE(FazerUnidade;PROCV(Unidade;MatrizUnidades;2;0);" ");

          TextoUnificado; ExtensoCentena & " " & ExtensoDezena & " " & ExtensoDez & " " & ExtensoUnidade;

          SUBSTITUIR(ARRUMAR(TextoUnificado);" ";" e ")
     )
)(F4#)

A ordem de grandeza é definida pela posição de cada item da matriz principal.

No nosso exemplo, com o número 2.419.491, temos 3 células, sendo: 2, 419 e 491, que vou indexar da maior para menor, ou seja: 3, 2 e 1, respectivamente. Com isso posso usar a função ESCOLHER, para colocar os textos de trilhão, bilhão, milhão, mil e sem grandeza, para os números 5, 4, 3, 2 e 1.

E, para conseguir preencher corretamente, preciso ainda descobrir se o número é singular ou plural… 🤯😵‍💫

Para definir a sequência para preencher as grandezas:

Célula AE4: =SEQUÊNCIA(H4;;H4;-1)

Para definir se é plural ou singular, uma fórmula simples:

Célula AF4: =G4#>1

Agora vem a função ESCOLHER, colocando a grandeza (na célula AG4):

=SE(AD4#=””;””;AD4#&” “&SE(AF4#;ESCOLHER(AE4#;””;”mil”;”milhões”;”bilhões”;”trilhões”);ESCOLHER(AE4#;””;”mil”;”milhão”;”bilhão”;”trilhão”)))

Estamos assim até o momento:

Se lermos, já está praticamente pronto: dois milhões quatrocentos e dezenove mil quatrocentos e noventa e um…

Porém, para juntarmos, precisamos definir algumas regrinhas da nossa língua portuguesa…

Temos que separar os itens por “, “ ou “ e “, dependendo de algumas regras…
Se o valor for único, precisa colocar a palavra “ de “ antes do “reais”…
Se for o último item, tem que colocar “real” ou “reais” no final
Tem de acrescentar os centavos no final, caso haja centavos

Para definir se vamos ou não colocar o “de”, são todos os valores superiores a um milhão, e que tenham combinações específicas de números… as próximas etapas vou explicar menos. Para inclusão da palavra “De” então, criei um VERDADEIRO ou FALSO (1 ou 0) na célula AG4:

=(A=(AD4#>=3)*(((P4#>=1)+(Q4#>0))+((SOMA(P4#;Q4#)=0)*(R4#>0))>0)*(SEERRO(–DIREITA(E4;NÚM.CARACT(E4)-LOCALIZAR(“.”;E4;(3*SEQUÊNCIA(G4;;0))+1));0)=0)*(SOMA(P4#)=P4#)*(SOMA(Q4#)=Q4#)

Aí, para realizar o tratamento do texto com o “De”, incluí na célula AH4:

=SE(AG4#;AF4#&” de”;AF4#)

Agora sim, podemos unificar os textos, separando por vírgulas, na célula AI4:

=UNIRTEXTO(“, “;VERDADEIRO;AH4#)

IncluIncluindo o texto de Moeda na célula AJ4:

=ARRUMAR(AI4&D4)

Agora vamos remover todas as vírgulas antes dos separadores “ e “, e também a vírgula antes da palavra “reais”, na célula AK4:

=SUBSTITUIR(SUBSTITUIR(AJ4;”, e”;” e”);”, r”;” r”)

Breve correção para casos encontrados em que o “e “ aparece no começo do texto, na célula AL4:

=SE(ESQUERDA(AK4;2) = “e “;EXT.TEXTO(AK4;3;NÚM.CARACT(AK4));AK4)

Por fim, o último tratamento é para remoção de vírgulas entre os textos unidos da matriz, na célula AM4:

=LET(
    C;ÍNDICE(R4#;G4);
    D;ÍNDICE(Q4#;G4);
    U;ÍNDICE(P4#;G4);
    txtTirar;", " & ARRUMAR(SUBSTITUIR(DIREITA(SUBSTITUIR(AL4;", ";REPT(", ";NÚM.CARACT(AL4)));NÚM.CARACT(AL4));", ";""));
    Alterar;SUBSTITUIR(AL4;txtTirar;SUBSTITUIR(txtTirar;", ";" e "));

    Tratar;OU(E(D+U=0;C>0);C=0);

    SE(Tratar;Alterar;AL4)
)

UFA!!

Considerações finais e consolidando em uma função unificada

Como o intuito não é termos de usar uma planilha de apoio para fazer a conversão, mas criar uma solução simples para reutilização, unifiquei todo o passo-a-passo que apresentei até agora em uma única célula, e o resultado ficou o seguinte:

=LAMBDA(Valor;
     LET(
          Superior;Valor>999999999999999;
          ValorInt;INT(Valor);
          Decimal;ARRED(Valor-ValorInt;2)*100;
          Moeda;SES(ValorInt=0;"";ValorInt=1;" real";1;" reais");
          ValorIntTXT;TEXTO(ValorInt;"#.##0");
          Split;FILTROXML("<t><r>"&SUBSTITUIR(ValorIntTXT;".";"</r><r>")&"</r></t>";"//r");
          QtdItens;CONT.VALORES(Split);
          SequenciaItens;SEQUÊNCIA(QtdItens;;QtdItens;-1);
          Plural;Split>1;

          Centena;INT(Split/100);
          Dezena;INT(DIREITA(Split;2)/10);
          Unidade;--DIREITA(Split);

          fxExtenso;
               LAMBDA(Valor;
                    LET(
                         Centena;INT(Valor/100);
                         Dezena;INT(DIREITA(Valor;2)/10);
                         Unidade;--DIREITA(Valor);

                         FazerCentena;Centena>0;
                         FazerDez;((Dezena=1)*(Unidade>0))=1;
                         FazerDezena;(NÃO(FazerDez)*(Dezena>0))=1;
                         FazerUnidade;(NÃO(FazerDez)*(Unidade>0))=1;

                         MatrizUnidades;{1\"um";2\"dois";3\"três";4\"quatro";5\"cinco";6\"seis";7\"sete";8\"oito";9\"nove"};
                         MatrizDez;{1\"onze";2\"doze";3\"treze";4\"quatroze";5\"quinze";6\"dezesseis";7\"dezessete";8\"dezoito";9\"dezenove"};
                         MatrizDezena;{1\"dez";2\"vinte";3\"trinta";4\"quarenta";5\"cinquenta";6\"sessenta";7\"setenta";8\"oitenta";9\"noventa"};
                         MatrizCentena;{1\"cento";2\"duzentos";3\"trezentos";4\"quatrocentos";5\"quinhentos";6\"seiscentos";7\"setecentos";8\"oitocentos";9\"novecentos"};

                         ExtensoCentena;SE(FazerCentena;SE(((Unidade=0)*(Dezena=0)*(Centena=1))=1;"cem";PROCV(Centena;MatrizCentena;2;0));" ");
                         ExtensoDezena;SE(FazerDezena;PROCV(Dezena;MatrizDezena;2;0);" ");
                         ExtensoDez;SE(FazerDez;PROCV(Unidade;MatrizDez;2;0);" ");
                         ExtensoUnidade;SE(FazerUnidade;PROCV(Unidade;MatrizUnidades;2;0);" ");

                         TextoUnificado; ExtensoCentena & " " & ExtensoDezena & " " & ExtensoDez & " " & ExtensoUnidade;

                         SUBSTITUIR(ARRUMAR(TextoUnificado);" ";" e ")
                   )
               );

          SplitExtenso;fxExtenso(Split);
          TextoMatriz;SE(SplitExtenso="";"";SplitExtenso&" "&SE(Plural;ESCOLHER(SequenciaItens;"";"mil";"milhões";"bilhões";"trilhões");ESCOLHER(SequenciaItens;"";"mil";"milhão";"bilhão";"trilhão")));
          IncluirDE;(SequenciaItens>=3)*(((Unidade>=1)+(Dezena>0))+((SOMA(Unidade;Dezena)=0)*(Centena>0))>0)*(SEERRO(--DIREITA(ValorIntTXT;NÚM.CARACT(ValorIntTXT)-LOCALIZAR(".";ValorIntTXT;(3*SEQUÊNCIA(QtdItens;;0))+1));0)=0)*(SOMA(Unidade)=Unidade)*(SOMA(Dezena)=Dezena);
          TextoMatrizDE;SE(IncluirDE;TextoMatriz&" de";TextoMatriz);
          TextoUnificado;UNIRTEXTO(", ";VERDADEIRO;TextoMatrizDE);
          TextoMoeda;TextoUnificado&Moeda;
          Correcoes1;SUBSTITUIR(SUBSTITUIR(ARRUMAR(TextoMoeda);", e";" e");", r";" r");
          Correcoes2;SE(ESQUERDA(Correcoes1;2) = "e ";EXT.TEXTO(Correcoes1;3;NÚM.CARACT(Correcoes1));Correcoes1);

          Correcoes3;
               LET(
                    C;ÍNDICE(Centena;QtdItens);
                    D;ÍNDICE(Dezena;QtdItens);
                    U;ÍNDICE(Unidade;QtdItens);
                    txtTirar;", " & ARRUMAR(SUBSTITUIR(DIREITA(SUBSTITUIR(Correcoes2;", ";REPT(", ";NÚM.CARACT(Correcoes2)));NÚM.CARACT(Correcoes2));", ";""));
                    Alterar;SUBSTITUIR(Correcoes2;txtTirar;SUBSTITUIR(txtTirar;", ";" e "));

                    Tratar;OU(E(D+U=0;C>0);C=0);

                    SE(Tratar;Alterar;Correcoes2)
               );

          txtCentavo; SE(Decimal = 0; "";SE(ValorInt=0;""; " e ") & fxExtenso(Decimal) & SE( Decimal = 1; " centavo"; " centavos"));

          Resultado;
          SE(Superior;
               "Valor Máximo Excedido";
               Correcoes3 & txtCentavo
          );

          ARRUMAR(Resultado)
     )
)(A2)

AEEEEEEEEEEEEEE 🍾🍾🍾🎖️🎖️🎖️🎆🎆🎆🎉🎉🎉

Mas alegria de pobre… sabem como é…

Quando fui jogar essa fórmula completa dentro da Caixa de Nomes, para transformar em uma função reutilizável, o Excel simplesmente não aceitou…

Descobri então que, apesar de o Excel aceitar fórmulas de até 8.192 caracteres, a caixa de nomes do Excel por sua vez aceita apenas 2.084 caracteres…

E agora? Minha fórmula ficou com 4.069…

Vamos às gambiarr… digo… ajustes técnicos novamente.

Removi todos os espaços de organização, identação, e substituí o nome de todas as variáveis para poucos caracteres. Enfim, temos a solução, que “coube como uma luva”, com seus 2.069 caracteres:

=LAMBDA(V;LET(S;V>(--REPT(9;15));VI;INT(V);Dc;ARRED(V-VI;2)*100;M;SES(VI=0;"";VI=1;" real";1;" reais");ViT;TEXTO(VI;"#.0");SP;FILTROXML("<t><r>"&SUBSTITUIR(ViT;".";"</r><r>")&"</r></t>";"//r");Q;CONT.VALORES(SP);Se;SEQUÊNCIA(Q;;Q;-1);Pl;SP>1;C;INT(SP/100);D;INT(DIREITA(SP;2)/10);U;--DIREITA(SP);fxEx;LAMBDA(V;LET(C;INT(V/100);D;INT(DIREITA(V;2)/10);U;--DIREITA(V);FzC;C>0;FzDez;((D=1)*(U>0))=1;FzD;(NÃO(FzDez)*(D>0))=1;FzU;(NÃO(FzDez)*(U>0))=1;MxUs;{1\"um";2\"dois";3\"três";4\"quatro";5\"cinco";6\"seis";7\"sete";8\"oito";9\"nove"};MxDez;{1\"onze";2\"doze";3\"treze";4\"quatroze";5\"quinze";6\"dezesseis";7\"dezessete";8\"dezoito";9\"dezenove"};MxD;{1\"dez";2\"vinte";3\"trinta";4\"quarenta";5\"cinquenta";6\"sessenta";7\"setenta";8\"oitenta";9\"noventa"};MxC;{1\"cento";2\"duzentos";3\"trezentos";4\"quatrocentos";5\"quinhentos";6\"seiscentos";7\"setecentos";8\"oitocentos";9\"novecentos"};ExC;SE(FzC;SE(((U=0)*(D=0)*(C=1))=1;"cem";PROCV(C;MxC;2;0));" ");ExD;SE(FzD;PROCV(D;MxD;2;0);" ");ExDez;SE(FzDez;PROCV(U;MxDez;2;0);" ");ExU;SE(FzU;PROCV(U;MxUs;2;0);" ");TxtU; ExC & " " & ExD & " " & ExDez & " " & ExU;SUBSTITUIR(ARRUMAR(TxtU);" ";" e ")));SPEx;fxEx(SP);Ls;"lhões";TxtMx;SE(SPEx="";"";SPEx&" "&SE(Pl;ESCOLHER(Se;"";"mil";"mi"&Ls;"bi"&Ls;"tri"&Ls);ESCOLHER(Se;"";"mil";"milhão";"bilhão";"trilhão")));IDe;(Se>=3)*(((U>=1)+(D>0))+((SOMA(U;D)=0)*(C>0))>0)*(SEERRO(--DIREITA(ViT;NÚM.CARACT(ViT)-LOCALIZAR(".";ViT;(3*SEQUÊNCIA(Q;;0))+1));0)=0)*(SOMA(U)=U)*(SOMA(D)=D);TMD;SE(IDe;TxtMx&" de";TxtMx);TxtU;UNIRTEXTO(", ";1;TMD);TxtM;TxtU&M;C_1;SUBSTITUIR(SUBSTITUIR(ARRUMAR(TxtM);", e";" e");", r";" r");C_2;SE(ESQUERDA(C_1;2) = "e ";EXT.TEXTO(C_1;3;NÚM.CARACT(C_1));C_1);C_3;LET(C;ÍNDICE(C;Q);D;ÍNDICE(D;Q);U;ÍNDICE(U;Q);tT;", " & ARRUMAR(SUBSTITUIR(DIREITA(SUBSTITUIR(C_2;", ";REPT(", ";NÚM.CARACT(C_2)));NÚM.CARACT(C_2));", ";""));Alt;SUBSTITUIR(C_2;tT;SUBSTITUIR(tT;", ";" e "));Tra;OU(E(D+U=0;C>0);C=0);SE(Tra;Alt;C_2));txtC; SE(Dc = 0; ""; SE(VI=0;""; " e ") & fxEx(Dc) & " centavo" & SE( Dc = 1; ""; "s"));R;SE(S;"Valor Máximo Excedido";C_3 & txtC);ARRUMAR(R)))(A2)

Basta copiar e colar na caixa de nomes, dando o nome que achar mais amigável.