Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Формула для парсинга номера, Как изящно с использованием формул, но без VBA убрать из номера вида AУУ0001B незначащие нули?
 
#1
04.09.2025 14:29:10
Добрый день.



Подскажите как изящно с использоваанием формул, но без VBA  убрать из маски формата AУУ00012B нули перед значащими цифрами?
Т.е. с помощью формулы получить AУУ12 из AУУ00012B (последний символ тоже желательно отсечь).

Все чуть усложняется тем, что маска может быть как AУУ00012B, так и AУУУ0013B  (один лишний символ У иногда меняется на дополнительный ноль перед значащими цифрами чтобы общее кол-во символов в номере не менялось).
Пока в голову приходят только громоздкие конструкции, отрезать последнюю букву справа, потом через REPLACE, избавиться от первых трех-четырех буквенных символов, и уже в конце работать с остатком вида 000012 переводя его в числовой формат, чтобы избавиться от незначащих нулей.

Может кто-то предложит более красивое решение?
Изменено: Algench - 04.09.2025 14:36:47
 
 
 
#2
04.09.2025 14:51:35
тут прям прямое применение PQ, причем из коробки мышкоклацем
 
 
 
#3
04.09.2025 14:54:04
Спасибо!
А где именно это в Power Query спрятано?
 
 
 
#4
04.09.2025 14:54:46
Код
=ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A1;ДЛСТР(A1)-1);"0";ПОВТОР(" ";10));10)))
Цитата
написал:
Может кто-то предложит более красивое решение?
А, так вам нужно красивое решение )
 
 
 
#5
04.09.2025 14:56:48
Algench, добрый день. Массивная:
=ЛЕВСИМВ(СЦЕП(ПОДСТАВИТЬ(ПСТР(D4;СТРОКА(ДВССЫЛ("J1:J" & ДЛСТР(D4)));1);"0";"")); ДЛСТР(СЦЕП(ПОДСТАВИТЬ(ПСТР(D4;СТРОКА(ДВССЫЛ("J1:J" & ДЛСТР(D4)));1);"0";"")))-1)

Algench, а может быть значение, например, таким AУУУ00130B, то есть 0 после значащих цифр?
Изменено: Alex - 04.09.2025 15:01:20
 
 
 
#6
04.09.2025 15:05:41
Цитата
написал:
а может быть значение, например, таким AУУУ00130B, то есть 0 после значащих цифр?
Да, конечно, это же обычный номер. Иначе бы я просто все нули поиском и заменой выпилил.  
 
 
 
#7
04.09.2025 15:10:55
Цитата
написал:
=ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(ЛЕВСИМВ(A1;ДЛСТР(A1)-1);"0";ПОВТОР(" ";10));10)))
Спасибо, это выглядит более изящно чем у меня получалось (с введением промежуточных столбцов).

Я уже даже начал думать насчет регулярных выражений. Это в принципе VBA, который я не хотел вмешивать, но с другой стороны там особого умения писать код не требуется, достаточно вставить модуль с функцией RegExpExtract если верить статье здесь https://www.planetaexcel.ru/techniques/7/4844/, а дальше работать с ней как с обычной формулой.
Правда никогда в экселе регулярными выражениями не пользовался, да и вообще много-много лет назад последний раз с ними сталкивался, но научиться никогда не поздно.
Изменено: Algench - 04.09.2025 15:11:50
 
 
 
#8
04.09.2025 15:16:29
Цитата
написал:
где именно это в Power Query спрятано
Данные --> Запросы и подключения
 
 
 
#9
04.09.2025 15:32:13
если "незначащие нули" всегда присутствуют:
=ЛЕВБ(A1;4)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);99)
 
 
 
#10
04.09.2025 15:37:47
Цитата
Algench написал:
отрезать последнюю букву справа, потом через REPLACE, избавиться от первых трех-четырех буквенных символов, и уже в конце работать с остатком вида 000012 переводя его в числовой формат, чтобы избавиться от незначащих нулей.
Примерно так и сделал
=ЛЕВБ(A1;ПОИСК(0;A1)-1)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);9)

Добавлено
Павел, прям клоны получились))) Я не подглядывал)))
Изменено: _Boroda_ - 04.09.2025 15:40:40
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#11
04.09.2025 16:08:59
off
Цитата
_Boroda_:   Павел, прям клоны получились))) Я не подглядывал)))
)  бывает ;-)
 
 
 
#12
04.09.2025 16:41:33
для версии 2021+ вот такой "монстр" получился. Но вроде как лишен недостатка
Цитата
написал:
если "незначащие нули" всегда присутствуют
=LET(_txt_base;A1;_txt1;ЕСЛИОШИБКА(ПСТР(_txt_base;1;ПОИСКПОЗ(1;--(ПСТР(_txt_base;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(_txt_base)));1)="0");0)-1);_txt_base);_txt2;ПСТР(ПРАВСИМВ(_txt_base;ДЛСТР(_txt_base)-ДЛСТР(_txt1));ЕСЛИ(СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСК({0;1;2;3;4;5;6;7;8;9};_txt1);0)>0))>0;1;ПОИСКПОЗ(1;--(ПСТР(ПРАВСИМВ(_txt_base;ДЛСТР(_txt_base)-ДЛСТР(_txt1));СТРОКА(ДВССЫЛ("1:"&ДЛСТР(_txt_base)));1)<>"0");0));ДЛСТР(_txt_base));_txt_result;_txt1&_txt2;ЛЕВСИМВ(_txt_result;ДЛСТР(_txt_result)-1))
 
 
 
#13
04.09.2025 17:08:00
Коллеги, всем огромное спасибо, все решения прекрасны, все попробую.
Но решения ПавелW и _Boroda_ чисто визуально выглядят симпатичнее ))
 
 
 
#14
04.09.2025 17:15:45
если впереди всегда 3/4 буквы не проще просто преобразовать в число с 4-ой позиции, а при ошибке с 5-ой?
=ЕСЛИОШИБКА(ЛЕВБ(A1;3)&(--ПСТР(A1;4;ДЛСТР(A1)-4));ЛЕВБ(A1;4)&(--ПСТР(A1;5;ДЛСТР(A1)-5)))

точнее достаточно 4-й символ проверять. =ЛЕВБ(A1;3)&ЕСЛИОШИБКА(--ПСТР(A1;4;1);ПСТР(A1;4;1))&(--ПСТР(A1;5;ДЛСТР(A1)-5))
Изменено: Sergey Stoyanov - 04.09.2025 17:30:39
 
 
 
#15
04.09.2025 17:59:56
_Boroda_, ПавелW,  Первые 4 если брать то надо ноль заменить в этой группе
=ПОДСТАВИТЬ(ЛЕВБ(A1;4);0;)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);99)
По вопросам из тем форума, личку не читаю.
 
 
 
#16
04.09.2025 21:58:55
для 365 некто buchlotnik предложил в личке
=РЕГЗАМЕНИТЬ(A1;"([A-ZА-Я]+)(0*)(\d+)(\D+)";"$1$3")

P.S. я так и написал ему в ответ, что новые функции убивают творчество :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#17
05.09.2025 08:19:16
Цитата
БМВ:  Первые 4 если брать
в примере надо первые 3 брать )
...
если "незначащих нулей" нет:
=ЛЕВБ(A1;3)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСКПОЗ(1=1;ИНДЕКС(ЕЧИСЛО(-ПСТР(A1;{1;2;3;4;5;6;7;8;9};1));););99)
 
 
 
#18
05.09.2025 08:37:59
Цитата
ПавелW написал:
в примере надо первые 3 брать )
Цитата
Algench написал:
Все чуть усложняется тем, что маска может быть как AУУ00012B, так и AУУУ0013B
По вопросам из тем форума, личку не читаю.
 
 
 
#19
05.09.2025 09:20:08
Цитата
БМВ написал:
_Boroda_ ,  ПавелW ,  Первые 4 если брать то надо ноль заменить в этой группе
А где ты у меня видишь первые 4?
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#20
05.09.2025 10:38:50
Цитата
БМВ: ...
угу
Цитата
Algench:   один лишний символ У иногда меняется на дополнительный ноль перед значащими цифрами
...и он становится "незначащим" со всеми вытекающими)
 
 
 
#21
05.09.2025 10:42:55
Цитата
_Boroda_написал:
Примерно так и сделал=ЛЕВБ(A1;ПОИСК(0;A1)-1)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);9)
Идеально работает!


Цитата
ПавелW, написал:
=ЛЕВБ(A1;4)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);99)
Не убирает первый незначащий ноль (четвертый по счету символ) для случая с тремя буквами в начале номера.

Цитата
ПавелW, написал:
=ЛЕВБ(A1;3)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСКПОЗ(1=1;ИНДЕКС(ЕЧИСЛО(-ПСТР(A1;{1;2;3;4;5;6;7;8;9};1)););99)
зато эта конструкция отлично работает



Цитата
Alex написал:
=LET(_txt_base;A1;_txt1;ЕСЛИОШИБКА(ПСТР(_txt_base;1;ПОИСКПОЗ(1;--(ПСТР(_txt_base;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(_txt_base)));1)="0");0)-1);_txt_base);_txt2;ПСТР(ПРАВСИМВ(_txt_base;ДЛСТР(_txt_base)-ДЛСТР(_txt1));ЕСЛИ(СУММПРОИЗВ(--(ЕСЛИОШИБКА(ПОИСК({0;1;2;3;4;5;6;7;8;9};_txt1);0)>0))>0;1;ПОИСКПОЗ(1;--(ПСТР(ПРАВСИМВ(_txt_base;ДЛСТР(_txt_base)-ДЛСТР(_txt1));СТРОКА(ДВССЫЛ("1:"&ДЛСТР(_txt_base)));1)<>"0");0));ДЛСТР(_txt_base));_txt_result;_txt1&_txt2;ЛЕВСИМВ(_txt_result;ДЛСТР(_txt_result)-1))
Я боюсь даже не смог понять что здесь написано :)  После перевода на английскую версию экселя у меня эта формула не отработала, и даже не знаю, причина в том что мне онлайн транслятор формулу перевел неправильно, или в том что у меня эксель 2016, а не 2021
Изменено: Algench - 05.09.2025 10:45:34
 
 
 
#22
05.09.2025 10:46:51
Цитата
БМВ написал:
=ПОДСТАВИТЬ(ЛЕВБ(A1;4);0;)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);99)
Вот эта тоже отлично работает.


Цитата
Sergey Stoyanov написал:
=ЕСЛИОШИБКА(ЛЕВБ(A1;3)&(--ПСТР(A1;4;ДЛСТР(A1)-4));ЛЕВБ(A1;4)&(--ПСТР(A1;5;ДЛСТР(A1)-5)))
Эта работает.

Цитата
Sergey Stoyanov написал:
точнее достаточно 4-й символ проверять. =ЛЕВБ(A1;3)&ЕСЛИОШИБКА(--ПСТР(A1;4;1);ПСТР(A1;4;1))&(--ПСТР(A1;5;ДЛСТР(A1)-5))
а эта первый ноль не заменяет.
Изменено: Algench - 05.09.2025 10:55:02
 
 
 
#23
05.09.2025 14:15:20
Еще раз отдельное спасибо _Boroda_ именно его формулу
Цитата
=ЛЕВБ(A1;ПОИСК(0;A1)-1)&--ПСТР(ЛЕВБ(A1;ДЛСТР(A1)-1);ПОИСК(0;A1);9)
взял за основу, минимально ее подправив. Да по правде вообще фактически и не правил, так, в одном месте ДЛСТР(A1)-1 на конкретное число заменил. :oops:  
 
 
 
#24
05.09.2025 14:39:32
Цитата
Algench написал:
в одном месте ДЛСТР(A1)-1 на конкретное число заменил
Получается, что код всегда состоит из девяти символов? Тогда все проще:
=ПОДСТАВИТЬ(ЛЕВБ(A1;4);0;)&--ПСТР(A1;5;4)
Или так:
=ТЕКСТ(ПСТР(A1;5;4);ЛЕВБ(A1;4)&"#")
Но ТЕКСТ в таком виде не будет корректно работать, если есть буквы ГМДЧС
Изменено: _Boroda_ - 05.09.2025 14:50:06
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#25
05.09.2025 16:30:52
с последней формулой в моем предыдущем сообщении я канеш поспешил, так правильно нужно было 4-й символ проверять.
=ЛЕВБ(A1;3)&ПСТР(A1;4;ПСТР(A1;4;1)<>"0")&--ПСТР(A1;5;4)
Но у _Boroda_ последнее сильно изящнее :)
 
 
 
#26
05.09.2025 17:49:57
Цитата
написал:
Получается, что код всегда состоит из девяти символов? Тогда все проще:

Не совсем. Код может быть разным, но значащая часть, которую надо вытащить состоит из 9 символов.
Условно говоря может быть так
AУУ0101200B
AУУУ001300B
а может так
AУУ0000001B-020-R45FFI-00
AУУУ000002B-020-R01FJF-00
или даже когда-нибудь так
AУУ0999998B-020-R9ORJL-00
AУУУ999999B-020-R50KJI-00
При этом маска первых11 символов всегда одна и та же, а первый и последний символ служебные и к номеру как к таковому отношения не имеют (я их обрезаю).

Цитата
написал:
Но ТЕКСТ в таком виде не будет корректно работать, если есть буквы ГМДЧС
Увы, буква М будет. Но вот этот короткий вариант =ПОДСТАВИТЬ(ЛЕВБ(A1;4);0;)&--ПСТР(A1;5;6) великолепен. Ровно то что надо. Коротко и красиво!


Вот итоговый вариант, который меня на 100% устраивает =SUBSTITUTE(MID(A1;2;3);0;)&--MID(A1;5;6)

Еще раз благодарю всех кто поучаствовал!
Изменено: Algench - 05.09.2025 18:08:59
 
 
 
Страницы: 1
Читают тему
Наверх