Olá seja bem vindo ao meu mais novo artigo!
Ao longo da minha carreira no MS Excel sempre me deparei com vários sistemas e com os dados que são extraído deles, dados inconsistentes, dados agrupados, dados chamados não-estruturados, eu os chamo de "dados deselegantes",
Segue abaixo um conjunto de dados "deselegantes"
As respostas encontradas em diversos sites são para exemplos com caracteres fixos, no caso da imagem acima, deve iniciar o recorte do valor no 4° caractere e retirar 4 caracteres, segue a fórmula abaixo para realizar essa estratégia no MS Excel.
=EXT.TEXTO(B3;4;4)*1ou em inglês
=MID(B3;4;4)*1Mas se tivéssemos caracteres variáveis? Qual seria a solução?
Visando este problema, desenvolvi abaixo dois exemplos dinâmico, retornando somente os valores numéricos de uma célula, segue
1° Exemplo - Texto-Valor-Texto
Neste exemplo, nota-se que os dados são não estruturados, onde não tem uma sequência lógica do aumento de caracteres e números. Para resolver esta questão, segue a fórmula dinâmica para a remoção dos caracteres de texto e retornando somente os caracteres numéricos
Fórmula na Célula C3
=EXT.TEXTO(B3;CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"&NUM.CARACT(B3) ));1)*1);0);0); AGREGAR(14;6;ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NUM.CARACT(B3)));1)*1)*LIN(INDIRETO("1:"& NUM.CARACT(B3)));1)-CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NUM.CARACT(B3)));1)*1);0);0)+1)*1ou em inglês
=MID(B3;MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW( INDIRECT("1:"& LEN(B3)));1)*1);0);0); AGGREGATE(14;6;ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1)*ROW(INDIRECT("1:"& LEN(B3)));1)-MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW( INDIRECT("1:"& LEN(B3)));1)*1);0);0)+1)*1Com esta fórmula é possível adicionar qualquer valor alfanumérico no MS Excel, retornando corretamente os valores numéricos.
Lembrando que esta fórmula irá remover somente um conjunto numérico da célula.
2° Exemplo - Texto-Valor-Texto-Valor-Texto
Caso seja necessário remover dois valores separados de uma célula deverá utilizar esta técnica, que segue abaixo
Fórmula na Célula C3
=EXT.TEXTO(B3;CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0);0); LOCALIZAR(0;CONCAT(ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3))) ;1)*1);0)); CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0);0)) -CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0);0))*1ou em inglês
=MID(B3;MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1);0);0);SEARCH(0;CONCAT(INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1);0));MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1);0);0))-MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3) ));1)*1);0);0))*1Fórmula na Célula D3
=EXT.TEXTO(B3;LOCALIZAR(1; CONCAT(ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0)); LOCALIZAR(0;CONCAT(ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0)); CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0);0))); AGREGAR(14;6; ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3)));1)*1)*LIN(INDIRECT("1:"& NÚM.CARACT(B3)));1)-LOCALIZAR(1;CONCAT(ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0)); LOCALIZAR(0;CONCAT(ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN( INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0)); CORRESP(1;ÍNDICE( - - ÉNÚM(EXT.TEXTO(B3;LIN(INDIRETO("1:"& NÚM.CARACT(B3)));1)*1);0);0)))+1)*1ou em inglês
=MID(B3;SEARCH(1;CONCAT(INDEX( - - ISNUMBER(MID(B3;ROW( INDIRECT("1:"& LEN(B3)));1)*1);0)); SEARCH(0;CONCAT(INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1);0));MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW( INDIRECT("1:"& LEN(B3)));1)*1);0);0))); AGGREGATE(14;6;ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1)*ROW(INDIRECT("1:"& LEN(B3)));1)-SEARCH(1;CONCAT(INDEX( - - ISNUMBER(MID(B3;ROW( INDIRECT("1:"& LEN(B3)));1)*1);0)); SEARCH(0;CONCAT(INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3)));1)*1);0));MATCH(1;INDEX( - - ISNUMBER(MID(B3;ROW(INDIRECT("1:"& LEN(B3) ));1)*1);0);0)))+1)*1Neste método é necessário utilizar uma fórmula chamada CONCAT, que pertence ao novo MS Excel 2019/Office 365
Segue o exemplo funcionando de maneira dinâmica.
3° Exemplo: Como realizar a soma de células com caracteres numéricos e texto
O desafio da soma com caracteres de texto foi demonstrado pelo meu amigo Mestre Rodrigo Aiosa, sendo que deverá realizar a soma dos valores numéricos, segue o exemplo abaixo:
Com os dados acima, nota-se um padrão, sendo o espaço que separa os valores numéricos dos valores de texto, com esta lógica definida foi possível realizar este desafio com a fórmula que segue abaixo,
Resposta na célula E2
=SOMA(ÍNDICE((ESQUERDA(ARRUMAR(A2:A20);LOCALIZAR(" ";ARRUMAR(A2:A20))-1))*1;0))ou em inglês
=SUM(INDEX((LEFT(TRIM(A2:A20);SEARCH(" ";TRIM(A2:A20))-1))*1;0))Segue o video da Live descrevendo passo a passo essas técnicas
4° Exemplo - Extração dos valores numéricos, independente da posição
Este desafio veio de uma pergunta do Gutemberg Silveira Alves
E a resposta é sim!
Segue o modelo acima conforme me comentou
Vamos a resposta! Segue abaixo.
=CONCAT(ÍNDICE(EXT.TEXTO(A2;AGREGAR(15;6;LIN(INDIRETO("1:"&NÚM.CARACT(A2)))/ÉNUM(EXT.TEXTO(A2;LIN(INDIRETO("1:"&NÚM.CARACT(A2)));1)*1);LIN(INDIRETO("1:"&SOMA(ÍNDICE(--ÉNUM(EXT.TEXTO(A2;LIN(INDIRETO("1:"&NÚM.CARACT(A2)));1)*1);0)))));1);0))*1ou em inglês
=CONCAT(INDEX(MID(A2;AGGREGATE(15;6;ROW(INDIRECT("1:"&LEN(A2)))/ISNUMBER(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1);ROW(INDIRECT("1:"&SUM(INDEX(--ISNUMBER(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1);0)))));1);0))*1E tem outra resposta do André Trevisan
=CONCAT(ÍNDICE(EXT.TEXTO(A2;LIN(INDIRETO("1:"&NÚM.CARACT(A2)));--ÉNUM(--EXT.TEXTO(A2;LIN(INDIRETO("1:"&NÚM.CARACT(A2)));1)));))ou em inglês
=CONCAT(INDEX(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));--ISNUMBER(--MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)));))Espero que tenha gostado desse artigo!
Caso tenha alguma sugestão de outra técnica ou melhoria do conteúdo acima, poste aqui nos comentários.
Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do MS Excel.
E ja conhece o meu novo curso online de Excel?
Abraços a todos e até o próximo artigo!
Fabio BALDINI
Frase do Dia: "You Never Change things by fighting the existing reality. To Change someting, build a new model that makes the existing model obsolete. (Você nunca muda as coisas lutando contra a realidade existente. Para mudar alguma coisa, crie um novo modelo que torne obsoleto o modelo existente)" Autor: Buckminster fuller
Link permanente
Muito obrigado por compartilhar sua experiência. Se possível me tira uma dúvida sobre qual a funcionalidade na fórmula abaixo do (*1)
=EXT.TEXTO(B3;4;4)*1
Link permanente
Olá Godofredo, formula ela serve para realizar uma extrair um pedaço de um texto, neste caso vai iniciar no 4° caracter e vai remover 4 caracteres, por isto a instrução 4, 4. Recomendo você realizar os nossos cursos, pois irá te ajudar e muito no entendimento correto das fórmulas, segue o link https://institutox.com.br/curso-online-de-excel/ Abraços
Link permanente
Só complementando o que o Mestre Baldini disse, o *1 significa que na extração deverá retornar um número, mas será entendido como texto, logo se utiliza deste artifício para “converter” o número de texto para “número propriamente dito”.
Link permanente
Isso mesmo Gutemberg, se você quiser o formato de Número deverá multiplicar por 1, mas se quiser deixar em texto, não será necessário essa multiplicação.
Abração
Link permanente
Mestre Baldini,
Muito obrigado pela resposta!!!
Havia chegado até CORRESP(VERDADEIRO;ÉNÚM(1*EXT.TEXTO(DH4;LIN(INDIRETO(“1:”&NÚM.CARACT(DH4)));1));0), mas, não consegui progredir. Daí resolvi pedir sua ajuda. Qual foi a lógica utilizada para se chegar neste resultado, por gentileza, pois pegar e copiar é mole, mas, o entender, é que é o divisor de águas. Pode me explicar, por gentileza.
Link permanente
Recomendo Gutemberg fazer os meus cursos para você entender passo a passo, onde você poderá resolver não só este problema, mas qualquer outro problema que vier surgir. Abraços
Link permanente
Muito bom e completo!
Uma pena que a “Fórmula na Célula C4” (e que na verdade seria D4) do 2º exemplo não funcionou, é justamente a que eu preciso.
Mas de qualquer forma vou salvar esse post, é muito útil.
Link permanente
Opa é verdade Obrigado Tiago arrumei e mesmo assim não funcionou? Que versão do Excel você está utilizando? Abraços
Link permanente
Show. Muito ótima suas dicas, você é fera. Parabéns
Abs. Carlos
Link permanente
Obrigadão Carlos 🙂
Link permanente
Excelente artigo!
Há uma forma de extrair os números de um texto que contém espaços?
Caso de uso: eu tenho um endereço que está preenchido em uma única célula e preciso separar o número do logradouro. Por exemplo: “Avenida Brasil, 101”
Indo mais além: E se o nome do logradouro realmente possuir um número? Por exemplo: “Rua 35, 1001” (é um exemplo real da cidade de Fortaleza)
Link permanente
Neste seu exemplo é mais facil primeiro separar os textos antes da virgula e depois da virgula, para depois fazer um tratamento no antes da virgula,
Espero ter ajudado
Link permanente
Bom dia Fabio
Tudo bem ?
Tentei usar uma de suas fórmulas para extrair somente números dos exemplos abaixo, mas não deu certo. Imagino que pela quantidade de espaços e caracteres diferentes.
Você consegue me ajudar com uma fórmula para extrair somente os números dos tipos de sequencia abaixo? Nesse caso ainda preciso separa o numero que esta dentro das barras e os dois algarismos (quando tiver) que estão entre a ultima barra e o traço.
BX.REC.1 /6151/01-APHN LOJA CONCE
BX.REC.1 /6151/02-APHN LOJA CONCE
BX.PAG. /18052024/01-FLB – JOINVILLE
PAG.ANTEC.ADT/70120-GERMANIA
BX.PAG. /8/07-ASSOCIACAO PORT
Link permanente
Sim tente remover os espaços e depois utilizar as funções de remoção, como a SUBSTITUIR o espaço por nada,
Abraços
Link permanente
Bom dia Professor,
Estou a usar o office 2019 e a formula para devolver os números (=CONCAT(ÍNDICE(EXT.TEXTO(A2;AGREGAR(15;6;LIN(INDIRETO(“1:”&NÚM.CARACT(A2)))/ÉNUM(EXT.TEXTO(A2;LIN(INDIRETO(“1:”&NÚM.CARACT(A2)));1)*1);LIN(INDIRETO(“1:”&SOMA(ÍNDICE(–ÉNUM(EXT.TEXTO(A2;LIN(INDIRETO(“1:”&NÚM.CARACT(A2)));1)*1);0)))));1);0))*1) está a dar erro. Eu tenho uma coluna onde tenho a seguinte descrição : “Chapa Preta 2mm” e gostaria que ele devolve-se noutra célula o nº 2. O que poderá estar a correr mal?
Cumprimentos
Link permanente
Acredito que seja o parenteses antes do igual, pois é obrigatório começar com o = e não o (
Abraços
Link permanente
Olá
Gostaria de compreender o “–” da função ÍNDICE(–
Parabéns pelo conteúdo.
Link permanente
Olá Guilherme, quando você coloca o –, é a mesma coisa que multiplicar por -1 * -1, sendo que o Excel vai alterar de Verdadeiro e Falso para 0 e 1, para realizar as operações matematicas. Isso é ensinado no meu curso nível 2. Abraços
Link permanente
Baldini,
No 2º exemplo não entendi como funciona a coluna D.
Usei o exemplo e a coluna C funcionou perfeitamente, quando fui pra coluna D não consegui fazer funcionar.
Link permanente
Olá José você está usando a nova versão do Excel? Abraços
Link permanente
Sensacional Fábio, uma lógica muito bem construída, parabéns
Link permanente
Oi Boa noite, fiquei com uma dúvida, porque você útilizou o “1:” na formula INDIRETO(“1:”
Link permanente
A função INDIRETO ela retorna o texto no intervalo do Excel, para fazer uma sequencia, onde nas novas funções no Excel poderia ser utilizado a função SEQUENCIA,
Abraços,
Link permanente
bom dia Professor Fábio,
Como realizado a separação do numero juntamente com textos, no entanto separando também o sinal tipo
Texto-Sinal -Valor-Texto-Valor-Texto
Link permanente
Sim, você poderá utilizar outros critérios também,
Abraços
Link permanente
Muito obrigada! Deu super certo!
Link permanente
Não funciona para números negativos
Link permanente
Só multiplicar por -1, fazer o tratamento e retornar multiplicando por -1,
Abraços,
Link permanente
Eu precisava utilizar essa formula para extrair somente os numeros de um CNPJ, exemplo: 01.234.567/0009-10, preciso somente dos números 01234567000910, ainda sim, eu preciso do numero do CNPJ raiz 01234567, porem ao extrair o cnpj raiz seria descartado o zero a esquerda, ficando 1234567 (01.234.567)
Link permanente
Olá Thiago, para fazer isso é mais simples só identificar os caracteres numericos, agrupar do maior para o menor e depois reagrupar,
Espero ter ajudado, Abraços
Link permanente
Fabio Bom dia,
Não estou consehuindo extrair dois numeros separadamente, pois la diz insira a formula da C3 e a outra formula na C4, porem no mini video tem a coluna D, poderia me expicar por favor
Link permanente
Ops é D4 e não C4,
Abraços Post corrigido! Obrigado
Link permanente
Bom dia Fábio,
Consegue me ajudar ? Eu tenho essas possíveis variáveis nos cadastros:
321VZ – Peso 325121
7864VZ – ZAVORR3536737M2
636VZ – 87650521 Weight
Eu preciso extrair apenas 4 números, no caso do primeiro exemplo 5121, no segundo 6737 e no terceiro 0521.
Link permanente
Olá Tomás tudo bom, desculpe a demora da resposta, mas segue
=IF(AVERAGE(SORT(LARGE(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1));SEQUENCE(4));1;1))=((LARGE(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1));4)+3+LARGE(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1));4))/2);CONCAT(INDEX(MID(A1;SEQUENCE(LEN(A1));1);SORT(LARGE(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1));SEQUENCE(4));1;1);0));CONCAT(INDEX(MID(A1;SEQUENCE(LEN(A1));1);SORT(LARGE(IF(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1))>=MAX(ABS(ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))-1)*SEQUENCE(LEN(A1)));0;ISNUMBER((–MID(A1;SEQUENCE(LEN(A1));1)))*SEQUENCE(LEN(A1)));SEQUENCE(4));1;1);0)))
Espero ter ajudado, Abraços!
Prof. Baldini
Link permanente
Olá Fábio.
Admiro muito pessoas com conhecimento, mais ainda as que de forma benevolente – compartilham o saber – é de enorme grandeza, espero que um dia essa seja uma das realidades que absorvem as obsoletas e mesquinhas do ego. Parabenizo você. Em tempo ajudou-me muito. Abraço.
Link permanente
Eu que agradeço o comentário Alexandre, um abraço!
Link permanente
Agradeço muito, estava procurando sem sucesso ate o momento, uma formula para extração de números, so localizava funções vba e atalhos, muito obrigado pelo compartilhamento desse conhecimento de grande ajuda!
Link permanente
Validação se o cliente é MEI ou não pela Razão Social. Não é 100%, mas ajuda muito.
=SEERRO(SE(NÚM.CARACT(EXT.TEXTO(B2;(NÚM.CARACT(B2)-11);NÚM.CARACT(B2)-11)*1)>=8;”MEI”;””);””)
Dá pra melhorar e validar o CPF no fim da razão, mas desse jeito o ajudou bastante.
A parte do SE onde valida a quantidade de cárceres pode variar. Acredito que vale apena tratar e colocar um validador de CPF.
Link permanente
Olá Fábio, tudo bem?
Estou ha um tempinho tentando resolver um caso no excel, mas sem sucesso, e já busquei no google, chatgpt e não achei.
Você saberia ajudar por favor?
Tenho uma coluna com dados não padronizados, onde contém letras, números, caracteres especiais, letras maísculas e minúsculas.
Preciso identificar a volumetria dos casos em que há letras em minúsculas.
Exemplo:
1) Part number = aXpQ12@ Resultado esperado de uma formula é “Sim”
2) Part number = AXPQ12@ Resultado esperado de uma formula é “Não”
Obrigada
Link permanente
Olá Cássia, você pode utilizar outras funções como PROCURAR para resolver o seu problema,
Espero ter ajudado,
Abraços
Prof. Baldini
Link permanente
Como extrair uma sequencia de números em meio a um texto? Por exemplo: “Há 14 itens de código 234568 sobrando no setor”.
Como extrair apenas os 6 números do código?
Link permanente
Olá Laercio, você pode utilizar o 2° Exemplo onde é possível extrair a 2 parte onde tem somente números, mas com a nova versão do Excel você pode colocar esse valor ao lado e em baixo apertar Ctrl+E que o Excel irá trazer todas as respostas que você precisa.
Espero ter ajudado,
Abraços
Prof. Baldini
Link permanente
Oi Fábio,
Consegue me ajudar ?
Preciso extrair da célula apenas sequências numericas com 6 ou mais carcteres. Os dados da células não possuem um padrão definido, algumas vezes nem apresentam a chave númerica.
Exemplo:
25V-TV-552980-CSJA-25X-BRBR3
TO PMW_775-WTV-98530061
BVB-IP-001/11QCE12X#1
107702-AC-BVB_159-WWF_BR
IP-001/11QCE12X_RRO_4230777_SREJPA4606P-532598 SS
O retorno seria:
552980
98530061
“Sem chave númerica”
107702
4230777 e 532598
Link permanente
Olá Julio tudo bom, agora nas novas versões do Excel você pode utilizar a função com IA do Excel que é o Ctrl+E onde ele vai identificar o padrão que você quer extrair os seus dados e irá te ajudar nessa extração.
Espero ter ajudado,
Prof. Baldini