Извлечение чисел из строки в Excel

Question

У меня есть столбец в Excel, который выглядит следующим образом:

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

Мне нужно извлечь 2941.10, 5410.02, 5431 и т.д ... Я попробовал следующую формулу: =MID(E4,MATCH(TRUE,ISNUMBER(1*MID(E4,ROW($3:$606),1)),0),COUNT(1*MID(E4,ROW($3:$606),1)))

Тем не менее, десятичное число в середине числа делает так, что последние два числа обрезаются. Не все числа имеют десятичную дробь, а количество текста после числа варьируется в зависимости от строки (поэтому левая и правая формулы не являются опциями). Я должен использовать формулу Excel, а не VBA/ макросы.

Какие-либо предложения?

Answer 1

использовать этот:

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

---

Приведенная выше формула предполагает, что числа всегда являются третьим "словом" в тексте. Если это не так И есть только одна группа чисел на строку, вы можете использовать эту формулу массива:

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

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter, а не Enter при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

Он перебирает слова и возвращает сумму всей группы чисел, и, поскольку существует только одна группа чисел, он возвращает именно это.

Answer 2

РЕДАКТИРОВАТЬ: я удалил свой первый ответ, поскольку я понимаю, что вы, вероятно, хотите вернуть строку цифр, включая начальные или конечные 0, и, по крайней мере, возможно, что строка цифр может быть длиннее 15 цифр.

Таким образом, приведенные ниже формулы возвращают число в виде текстовой строки, сохраняя любые начальные и конечные нули. например, 2941.10 возвращается как таковой, а не как 2941.1

Вот одно решение формулы

Сначала определите seq как именованную формулу:

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

seq вернет массив чисел {1...255} В отличие от метода ROW(INDIRECT(... ) для возврата массивов, эта формула является энергонезависимой.

Затем вы можете использовать эту формулу массива, введенную, удерживая Ctrl + Shift при нажатии 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)

или вы можете использовать это немного дольше, но обычно вводите формулу:

=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)

Если это так, как в вашем примере, последовательность цифр всегда начинается с 14-й позиции, тогда вы можете использовать эту упрощенную, обычно вводимую формулу:

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