Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск и вывод чисел из текста в отдельные ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и вывод чисел из текста в отдельные ячейки (Формулы/Formulas)
Поиск и вывод чисел из текста в отдельные ячейки
4shark Дата: Пятница, 29.03.2019, 17:37 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Уважаемые форумчане, помогите решить производственную проблему.
В ячейку записан осмысленный текст, слова чередуются с цифрами. Цифры - целые одно-двух-трехзначные числа. Необходимо эти цифры найти и разместить в том же найденном порядке по отдельным ячейкам (строкам или столбцам). Также в тексте присутствуют буквенные сокращения (ориентировочно 10 видов). Их необходимо перевести и вывести в следующих ячейках после цифр. Для буквенных сокращений буден создана таблица сопоставлений, откуда можно будет выбирать новое значение. Вроде ясно описал)
Нашел решение для поиска одного числа, но вот переделать эту формулу для своей задачи затрудняюсь.
 
Ответить
СообщениеУважаемые форумчане, помогите решить производственную проблему.
В ячейку записан осмысленный текст, слова чередуются с цифрами. Цифры - целые одно-двух-трехзначные числа. Необходимо эти цифры найти и разместить в том же найденном порядке по отдельным ячейкам (строкам или столбцам). Также в тексте присутствуют буквенные сокращения (ориентировочно 10 видов). Их необходимо перевести и вывести в следующих ячейках после цифр. Для буквенных сокращений буден создана таблица сопоставлений, откуда можно будет выбирать новое значение. Вроде ясно описал)
Нашел решение для поиска одного числа, но вот переделать эту формулу для своей задачи затрудняюсь.

Автор - 4shark
Дата добавления - 29.03.2019 в 17:37
ASWP Дата: Пятница, 29.03.2019, 18:11 | Сообщение № 2
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 183
Репутация: 24 ±
Замечаний: 0% ±

Excel 2007
4shark, А где файл с примером?


я знаю что ничего не знаю, но другие не знают и этого
 
Ответить
Сообщение4shark, А где файл с примером?

Автор - ASWP
Дата добавления - 29.03.2019 в 18:11
4shark Дата: Пятница, 29.03.2019, 19:12 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
пардон, забыл
К сообщению приложен файл: 7336197.xls (29.0 Kb)
 
Ответить
Сообщениепардон, забыл

Автор - 4shark
Дата добавления - 29.03.2019 в 19:12
_Boroda_ Дата: Пятница, 29.03.2019, 20:59 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Чет у меня к вечеру не соображается, монстры какие-то лезут
Код
=ЕСЛИОШИБКА(ВПР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222));СИМВОЛ(10););E$3:F$99;2;);СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222)))

Или в 2 столбца
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222))

Код
=ЕСЛИОШИБКА(ВПР(ПОДСТАВИТЬ(G2;СИМВОЛ(10););E$3:F$9;2;);D2)

Или в именованный диапазон

В файле переписал кое-что. Красным пометил
К сообщению приложен файл: 7336197_1.xlsx (10.3 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЧет у меня к вечеру не соображается, монстры какие-то лезут
Код
=ЕСЛИОШИБКА(ВПР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222));СИМВОЛ(10););E$3:F$99;2;);СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222)))

Или в 2 столбца
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПРОСМОТР(;-1/(A$2:A2<>"");A$2:A2);B3;ЕСЛИ(A3="";ПОВТОР(" ";222);B3));B2;ПОВТОР(" ";222));222;222))

Код
=ЕСЛИОШИБКА(ВПР(ПОДСТАВИТЬ(G2;СИМВОЛ(10););E$3:F$9;2;);D2)

Или в именованный диапазон

В файле переписал кое-что. Красным пометил

Автор - _Boroda_
Дата добавления - 29.03.2019 в 20:59
4shark Дата: Пятница, 29.03.2019, 21:26 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Чет у меня к вечеру не соображается, монстры какие-то лезут

Большое спасибо.
Однако не совсем корректно работает с другими значениями. Поставщик видимо прайс видимо формировал спонтанно. Перед некоторыми цифрами могут стоять символы ":", "-" "мм" в разных комбинациях. Цифры от текста и символов иногда не отделяются пробелами. В этом случае форма не работает.


Сообщение отредактировал 4shark - Пятница, 29.03.2019, 21:36
 
Ответить
Сообщение
Чет у меня к вечеру не соображается, монстры какие-то лезут

Большое спасибо.
Однако не совсем корректно работает с другими значениями. Поставщик видимо прайс видимо формировал спонтанно. Перед некоторыми цифрами могут стоять символы ":", "-" "мм" в разных комбинациях. Цифры от текста и символов иногда не отделяются пробелами. В этом случае форма не работает.

Автор - 4shark
Дата добавления - 29.03.2019 в 21:26
_Boroda_ Дата: Пятница, 29.03.2019, 21:39 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Как Вы думаете, то, что
не совсем корректно работает с другими значениями
- это из-за чего? Из-за того, что я не догадался, что поставщик у Вас баловник такой? Или из-за того, что Вы не предоставили в полном объеме материал для написания формулы?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеКак Вы думаете, то, что
не совсем корректно работает с другими значениями
- это из-за чего? Из-за того, что я не догадался, что поставщик у Вас баловник такой? Или из-за того, что Вы не предоставили в полном объеме материал для написания формулы?

Автор - _Boroda_
Дата добавления - 29.03.2019 в 21:39
4shark Дата: Пятница, 29.03.2019, 22:33 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
это из-за чего? Из-за того, что я не догадался, что поставщик у Вас баловник такой? Или из-за того, что Вы не предоставили в полном объеме материал для написания формулы?

Дык я без претензий). Поблагодарил. Сам особо не проглядел все комбинации. Просто сейчас взял данные из другого конца прайса и вставил. Думал предложенный алгоритм будет обходить все эти косяки или будет работать независимо от них. В любом случае еще раз спасибо.
Возможно кто то еще предложит варианты


Сообщение отредактировал 4shark - Суббота, 30.03.2019, 18:42
 
Ответить
Сообщение
это из-за чего? Из-за того, что я не догадался, что поставщик у Вас баловник такой? Или из-за того, что Вы не предоставили в полном объеме материал для написания формулы?

Дык я без претензий). Поблагодарил. Сам особо не проглядел все комбинации. Просто сейчас взял данные из другого конца прайса и вставил. Думал предложенный алгоритм будет обходить все эти косяки или будет работать независимо от них. В любом случае еще раз спасибо.
Возможно кто то еще предложит варианты

Автор - 4shark
Дата добавления - 29.03.2019 в 22:33
4shark Дата: Суббота, 30.03.2019, 22:38 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
подкину расширенный пример
К сообщению приложен файл: 5810709.xls (29.5 Kb)
 
Ответить
Сообщениеподкину расширенный пример

Автор - 4shark
Дата добавления - 30.03.2019 в 22:38
bmv98rus Дата: Воскресенье, 31.03.2019, 11:29 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Порой универсальность формул менее важна, чем результат. Попробуйте так.
К сообщению приложен файл: Copy_of_777.xlsx (10.5 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеПорой универсальность формул менее важна, чем результат. Попробуйте так.

Автор - bmv98rus
Дата добавления - 31.03.2019 в 11:29
4shark Дата: Воскресенье, 31.03.2019, 14:21 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, спасибо, интересно сделано. Т.е. поисковые формулы решены отдельно каждый по своей категории.
Однако вот такой еще вариант не отрабатывают. Гендерная принадлежностьWomanразмер. Т.е. слитное написание
К сообщению приложен файл: 5437159.xls (32.0 Kb)
 
Ответить
Сообщениеbmv98rus, спасибо, интересно сделано. Т.е. поисковые формулы решены отдельно каждый по своей категории.
Однако вот такой еще вариант не отрабатывают. Гендерная принадлежностьWomanразмер. Т.е. слитное написание

Автор - 4shark
Дата добавления - 31.03.2019 в 14:21
bmv98rus Дата: Воскресенье, 31.03.2019, 15:03 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Ну если данное описание всегда присутствует , то можно совсем просто
К сообщению приложен файл: Copy_of_777_1.xlsx (10.4 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеНу если данное описание всегда присутствует , то можно совсем просто

Автор - bmv98rus
Дата добавления - 31.03.2019 в 15:03
4shark Дата: Воскресенье, 31.03.2019, 15:45 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, еще раз спасибо. Сейчас распознает все четко. Единственное, хотел бы увеличить диапазон строк для таблицы сопоставлений на случай большего количества цветовых комбинаций. При попытке увеличить в формуле распознавания цветов диапазон до 20 строк, т.е. $F$6:$F$20 и $E$6:$E$20 выдает "нет данных".
 
Ответить
Сообщениеbmv98rus, еще раз спасибо. Сейчас распознает все четко. Единственное, хотел бы увеличить диапазон строк для таблицы сопоставлений на случай большего количества цветовых комбинаций. При попытке увеличить в формуле распознавания цветов диапазон до 20 строк, т.е. $F$6:$F$20 и $E$6:$E$20 выдает "нет данных".

Автор - 4shark
Дата добавления - 31.03.2019 в 15:45
Светлый Дата: Воскресенье, 31.03.2019, 18:30 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Для гендерной принадлежности:
Код
=ПРОСМОТР(999;ПОИСК(E$3:E$5;A2);F$3:F$5)
Для числовых значений (3 строки) массивная:
Код
=-МИН(ЕСЛИОШИБКА(-ПРАВБ(ПСТР(A2;ПОИСК(B3;A2);СТРОКА($1:$30));{1;2;3});))
Для цвета (2 строки):
Код
=ПРОСМОТР(999;ПОИСК(E$6:E$9;ПСТР(A2;ПОИСК(B6;A2);40));F$6:F$9)
Формулы копируются блоком.
К сообщению приложен файл: 5810709-2.xls (33.0 Kb)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДля гендерной принадлежности:
Код
=ПРОСМОТР(999;ПОИСК(E$3:E$5;A2);F$3:F$5)
Для числовых значений (3 строки) массивная:
Код
=-МИН(ЕСЛИОШИБКА(-ПРАВБ(ПСТР(A2;ПОИСК(B3;A2);СТРОКА($1:$30));{1;2;3});))
Для цвета (2 строки):
Код
=ПРОСМОТР(999;ПОИСК(E$6:E$9;ПСТР(A2;ПОИСК(B6;A2);40));F$6:F$9)
Формулы копируются блоком.

Автор - Светлый
Дата добавления - 31.03.2019 в 18:30
4shark Дата: Воскресенье, 31.03.2019, 19:12 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, отлично! Спасибо большое. Единственное еще бы сделать чтобы скрыть в случае отсутствия цвета "Нет данных". Попробую сам конечно доработать
Всем участникам огромное мерси!
 
Ответить
СообщениеСветлый, отлично! Спасибо большое. Единственное еще бы сделать чтобы скрыть в случае отсутствия цвета "Нет данных". Попробую сам конечно доработать
Всем участникам огромное мерси!

Автор - 4shark
Дата добавления - 31.03.2019 в 19:12
bmv98rus Дата: Воскресенье, 31.03.2019, 20:57 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, Если в таблице соответствий будет например простой цвет, например Gold, то …. я с длинной слова по тому и мудрил.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый, Если в таблице соответствий будет например простой цвет, например Gold, то …. я с длинной слова по тому и мудрил.

Автор - bmv98rus
Дата добавления - 31.03.2019 в 20:57
Светлый Дата: Воскресенье, 31.03.2019, 22:53 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
я с длиной слова по тому и мудрил
bmv98rus, я полагаю, это можно сделать 1 раз - отсортировать по длине таблицу перевода. Более короткие слова перенести в начало списка. Тогда проблем не возникает и формула проще.
Ещё в таблице был образец с пробелами в конце. Вместо использования СЖПРОБЕЛЫ просто удалил их.
Если бы образцы были неизвестно откуда и постоянно менялись бы, тогда конечно...


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
я с длиной слова по тому и мудрил
bmv98rus, я полагаю, это можно сделать 1 раз - отсортировать по длине таблицу перевода. Более короткие слова перенести в начало списка. Тогда проблем не возникает и формула проще.
Ещё в таблице был образец с пробелами в конце. Вместо использования СЖПРОБЕЛЫ просто удалил их.
Если бы образцы были неизвестно откуда и постоянно менялись бы, тогда конечно...

Автор - Светлый
Дата добавления - 31.03.2019 в 22:53
bmv98rus Дата: Воскресенье, 31.03.2019, 23:10 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 31.03.2019 в 22:53, в сообщении № 16 ()
Тогда проблем не возникает и формула проще.
спору нет.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 31.03.2019 в 22:53, в сообщении № 16 ()
Тогда проблем не возникает и формула проще.
спору нет.

Автор - bmv98rus
Дата добавления - 31.03.2019 в 23:10
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и вывод чисел из текста в отдельные ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!