Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выцепить цифры из содержимого ячейки 2, Извлечь цифры из текста формулами
 
#1
24.04.2022 12:52:39
Добрый день. Ранее такая тема уже была, но она в архиве.
Помогите написать формулу (макрос нельзя), чтобы извлечь девять цифр ЛС.
По условию девять цифр могут стоят в любом месте текста (начало, середина, конец). В тексте могут быть и другие цифры, их количество не равно 9.

Ячейка А1: Оплата счета №5085 от 12.09.21. ЛС 123456789 за услуги ремонта.
 
 
 
#2
24.04.2022 13:43:55
Цитата
DCT написал:
Ранее такая тема уже была, но она в архиве.
тогда в чем проблема, если вы нашли то примените то решение, адаптировав по себя?
По вопросам из тем форума, личку не читаю.
 
 
 
#3
25.04.2022 09:23:51
Добрый.
Можно тут почитать.
Кому решение нужно - тот пример и рисует.
 
 
 
#4
25.04.2022 10:28:19
Цитата
Пытливый написал:
Можно  тут почитать.
А толку
Цитата
DCT написал:
(макрос нельзя),
 
 
 
#5
25.04.2022 11:51:09
DCT, Можно такой вариант:
Код
=FILTERXML("<k><m>"&SUBSTITUTE($A1;" ";"</m><m>")&"</m></k>";"//m[string-length()=9 and number()]")

Если строк много, то будет подтормаживать.
 
 
 
#6
25.04.2022 12:10:42
memo, слишком мало примеров дал ТС.
например крохотное изменение
Оплата счета №5085 от 12.09.21. ЛС 123456789, за услуги ремонта.
или
Оплата счета №5085 от 12.09.21. ЛС123456789 за услуги ремонта.
и формула не работает.
нужно проверять экранируя  или по условию. что 9 символов начиная с N число а 10 и 9 начиная с N-1 не число.
По вопросам из тем форума, личку не читаю.
 
 
 
#7
25.04.2022 14:32:23
БМВ, Ну если ТС нужно более менее универсальное решение, то вот, безо всякого изобретения велосипеда.
Примеры в файле, насколько хватило фантазии.
=MAX(IFERROR(VALUE(MID(A1;ROW($A$1:$A$300);9));0)) (формула массива)
В FILTER.XML придется заморочиться с длинной чередой SUBSTITUTE() и подбором параметров для xPath.
Изменено: memo - 25.04.2022 14:35:15
 
 
 
#8
25.04.2022 14:57:13
memo,
Медведь такой фантазер
Оплата счета от 12.09.21. за услуги №5085##000033333? ремонта.
трудно конечно поверить что  ЛС может быть с нулями но я о методе скорее. что мы получим. это 33333 из последней последовательности и 44451 из 12.09.21
ок изменим чуток =MAX(IFERROR(--MID(A9;ROW($A$1:$A$300);9);0)) что даст 33333, но
Оплата счета от 12.09.2021. за услуги №5085##000033333? ремонта. - снова вернет проблему.
По вопросам из тем форума, личку не читаю.
 
 
 
#9
25.04.2022 15:00:20
Прикольно, обсуждают все кроме ТС, и в первых рядах БМВ, а нужно всего-то файл с примером  :D
 
 
 
#10
25.04.2022 15:24:11
Цитата
Msi2102: в первых рядах  БМВ
МОДЕРАТОР БМВ, на секундочку)))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#11
25.04.2022 15:35:20
Цитата
Jack Famous написал:
МОДЕРАТОР БМВ, на секундочку)))
Так и я о том же, пора применять административный ресурс, а не пытаться решить проблему вместе с нами  :D
 
 
 
#12
25.04.2022 15:48:18
Off
Ну ка флуд прекратили!!! :-) Это я режим модератора включил.:-)

Цитата
Msi2102 написал:
а нужно всего-то файл с примером
и да и нет. Пример может не содержать того что появится в реальности. Как формулист, а не как модератор, я  смотрю на решения и вижу те или иные моменты. У меня накидан вариант поиска именно последовательности из 9 цифр, но он длинноват. очень длинноват. дома подумаю еще, на работе другим занят.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
25.04.2022 17:16:04
Цитата
написал:
снова вернет проблему
А если использовать ПОИСКПОЗ, то даже в предложенном варианте возвращает девять "текстовых" цифр
Код
=ПСТР(A1;ПОИСКПОЗ(1;--ЕЧИСЛО(--ПСТР(A1;СТРОКА(1:100);9));0);9)
 
 
 
#14
25.04.2022 18:30:50
IKor, не верю.
По вопросам из тем форума, личку не читаю.
 
 
 
#15
25.04.2022 18:39:52
Я не понимаю почему, но сначала моя формула с тестовой строкой вернула дату.
Я опечалился.
И решил проверить с оригинальным текстом ТС'а
Вернулись нужные цифры.
Потом я подставил обратно тестовую строку из #8.
Цифры остались.
Боюсь дышать теперь...
Изменено: IKor - 25.04.2022 18:40:19
 
 
 
#16
25.04.2022 18:44:58
IKor, Всё получается, но если перед цифрами будет пробел, то в результат начинается пробела и съедается последняя цифра
Изменено: Msi2102 - 25.04.2022 18:46:11
 
 
 
#17
25.04.2022 18:54:41
Msi2102, в тестовом примере #8 пробел не мешает и не съедает последнюю из 9 цифр...  Но только для нового варианта - см. ниже
А вот если дата окажется в "полном формате", то она подменит собой нужный результат.
В качестве временной "заглушки" можно обнулять такие результаты, но при этом есть риск пропустить нужные индексы с четырьмя ведущими нулями... как в примере БМВ
Код
=ПСТР(A1;ПОИСКПОЗ(1;--(--ПСТР(A1;СТРОКА(1:100);9)>100000);0);9)
Как ни обидно признавать, но даты перед индексом мешают ловить индексы с четырьмя ведущими нулями :(
Изменено: IKor - 25.04.2022 19:01:54
 
 
 
#18
25.04.2022 18:59:52
IKor, если между № и первой цифрой будет пробел, результат " 00003333", а не "000033334"
Оплата счета от 12.09.21. за услуги №5085 № 000033334? ремонта.
Вот костыль для полной даты
=ПСТР(A1;ПОИСКПОЗ(1;--ЕЧИСЛО(--ПСТР(ПОДСТАВИТЬ(A1;".";"а");СТРОКА(1:100);9));0);9)
Изменено: Msi2102 - 25.04.2022 19:05:14
 
 
 
#19
25.04.2022 19:08:49
Msi2102, Если использовать отредактированный вариант формулы из #17, то пробелы перед индексом не мешают... в отличии от четырёх ведущих нулей в индексе.
Изменено: IKor - 25.04.2022 19:09:04
 
 
 
#20
25.04.2022 19:19:15
Отличный костыль!
Его же можно применить и к пробелам... Тогда получается такой вариант на двух костылях
Код
=ПСТР(A1;ПОИСКПОЗ(1;--(--ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;" ";"б");".";"а");СТРОКА(1:100);9)>=0);0);9)
 
 
 
#21
25.04.2022 19:25:17
Даже страшно уже. Клик влево, клик вправо и ик))
Цитата
БМВ написал:
режим модератора
Даже не знаю, поздравлять или сочувствовать. Лучше все-таки поздравлю:)
По теме, рискну на еще одну попытку.
=TEXT(MAX(IFERROR(VALUE(MID(SUBSTITUTE(A1;",";"!");ROW(INDIRECT("1:"&LEN(A1)));9)+0);""));"000000000")
Изменено: memo - 25.04.2022 19:26:01
 
 
 
#22
25.04.2022 19:31:14
Цитата
IKor написал:
Его же можно применить и к пробелам
Я его уже писать не стал, вторая формула хорошо работает, только копи_паститься не хочет, #НД выдает, почему не стал разбираться, у Вас примере работает :)
 
 
 
#23
25.04.2022 19:39:05
Уже лучше, но дата может быть с - и с / то есть всем не хватает замены этих символов на что-то иное то есть замене подлежат пробел и ,.-/
скажем так
=MID(A11;MATCH(1=1;-MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A11;" ";"!");".";"!");",";"!");"-";"!");"/";"!");ROW($1:$100);9)<=0;);9)
ну и что мне не нравится , но это заложено в условии ТС, то это то, что все остальные группы цифр меньше 9.
Это наверно только для фанатов. Ищет последовательно 9 цифр не больше не меньше
=MID(A1;SUM(IF(ROW($1:$99)<MATCH(9;FREQUENCY(IF(IFERROR(ABS(MID(A1;ROW($1:$99);1)-5);6)<6;ROW($1:$99));IF(IFERROR(ABS(MID(A1;ROW($1:$99);1)-5);6)>5;ROW($1:$99))););IFERROR(INDEX(FREQUENCY(IF(IFERROR(ABS(MID(A1;ROW($1:$99);1)-5);6)<6;ROW($1:$99));IF(IFERROR(ABS(MID(A1;ROW($1:$99);1)-5);6)>5;ROW($1:$99)));N(INDEX(ROW($1:$99);)))+1;)))+1;9)
По вопросам из тем форума, личку не читаю.
 
 
 
#24
26.04.2022 10:47:13
БМВ,
Я правильно понимаю, что оборот -ПСТР(...)<=0 был выбран потому, что он на один символ короче предложенного выше?
А вариант ПОИСКПОЗ(1=1;...) на целых две скобки и последний аргумент?

В таком случае для чего "удлинять" конструкцию СТРОКА($1:$99) абсолютными привязками? Пусть бы себе относительные ссылки скользили вместе с формулами...
 
 
 
#25
26.04.2022 12:33:59
Цитата
IKor написал:
В таком случае для чего "удлинять" конструкцию СТРОКА($1:$99) абсолютными привязками?
для того чтоб получилось правильно :-)
Если серьезно, то если не закрепить, то будут браться не с первого символа из последующих строк  и результат в результате станет не корректным
По вопросам из тем форума, личку не читаю.
 
 
 
#26
04.05.2022 17:16:01
Всем большое спасибо за комментарии. Помогли разобраться!
 
 
 
Страницы: 1
Наверх