Extrai números de uma string no Excel

Question

Eu tenho uma coluna no excel que se parece com isso:

Census Tract 2941.10 (part), Carson city, Compton CCD, Los Angeles County, California
Census Tract 5410.02, Carson city, Compton CCD, Los Angeles County, California
Census Tract 5431 (part), Carson city, Compton CCD, Los Angeles County, California
Census Tract 5432.02 (part), Carson city, Compton CCD, Los Angeles County, California
Census Tract 5433.04, Carson city, Compton CCD, Los Angeles County, California

Eu preciso extrair 2941.10, 5410.02, 5431, etc ... Eu tentei a seguinte fórmula: =MID(E4,MATCH(TRUE,ISNUMBER(1*MID(E4,ROW($3:$606),1)),0),COUNT(1*MID(E4,ROW($3:$606),1)))

No entanto, o decimal no meio do número faz com que os dois últimos números sejam cortados. Nem todos os números têm um decimal e a quantidade de texto após o número varia de acordo com a linha (as fórmulas esquerda e direita não são opções). Preciso usar uma fórmula do Excel e não VBA / macros.

Alguma sugestão?

    

Answer 1

use isso:

=--TRIM(SUBSTITUTE(MID(SUBSTITUTE(A1," ",REPT(" ",999)),2*999,999),",",""))

Afórmulaacimaassumequeosnúmerossãosempreaterceira"palavra" no texto. Se não for esse o caso AND existe apenas um grupo de números por string, você pode usar esta fórmula de matriz:

=SUM(IFERROR(--TRIM(SUBSTITUTE(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)*999+1,999),",","")),0))

Sendo uma fórmula de matriz, ela precisa ser confirmada com Ctrl-Shift-Enter, em vez de Enter ao sair do modo de edição. Se feito corretamente, o Excel colocará {} em torno da fórmula.

Ele itera as palavras e retorna a soma de todos os números, e como há apenas um grupo numérico, ele retorna exatamente isso.

    

Answer 2

EDITAR: eliminei minha primeira resposta, pois percebo que você provavelmente deseja retornar uma sequência de dígitos, incluindo iniciais ou finais 0, e é pelo menos concebível que a cadeia de dígitos pode ter mais de 15 dígitos.

Portanto, as fórmulas abaixo retornam o número como uma string de texto, preservando qualquer zero inicial e final. por exemplo, 2941.10 é retornado como tal e não como 2941.1

Aqui está uma solução de fórmula

Primeiro, defina seq como uma fórmula nomeada:

seq Refers To:  =ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))

seq retornará uma matriz de números {1...255} Diferente do método ROW(INDIRECT(... de retornar matrizes, essa fórmula não é volátil.

Então você pode usar esta fórmula de matriz digitando ctrl + shift enquanto pressiona enter

=MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,seq,1)),0),LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)-MATCH(TRUE,ISNUMBER(-MID(A1,seq,1)),0)+1)

ou, você pode usar essa fórmula um pouco mais longa, mas normalmente inserida:

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

Se for o caso, como no seu exemplo, que a sequência de dígitos sempre começa na 14ª posição, então você pode usar essa fórmula simplificada, normalmente inserida:

=MID(A1,14,LOOKUP(2,1/ISNUMBER(-MID(A1,seq,1)),seq)-13)