Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Извлечение частей текста, разделенных двумя символами
 
#1
23.06.2019 13:52:21
Добрый день!

Имеются ячейки с текстом (1700-1800 знаков в такой ячейке).
Текст представляет из себя тридцать вот такого рода груп "u2q8o0a9e6e6e6q8_aston-martin-db10-silver-car-png-image-scalextric" перечисленных через запятую без пробелов.
Необходимо вытащить на другие ячейки первые части этих групп. То есть то, что стоит до символа "_", в примере выше - это "u2q8o0a9e6e6e6q8".


На форуме я нашел формулу, которая вытаскивает слова, написанные через пробел, на новые ячейки:
Код
=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A2;" ";ПОВТОР(" ";ДЛСТР($A2)));ДЛСТР($A2)*(СТОЛБЕЦ(A:A)-1)+1;ДЛСТР($A2)))
Доработал её, чтобы сразу оставались лишь значения до знака "_":
Код
=ЛЕВСИМВ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A5;" ";ПОВТОР(" ";ДЛСТР($A5)));ДЛСТР($A5)*(СТОЛБЕЦ(A:A)-1)+1;ДЛСТР($A5)));ПОИСК("_";СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($A5;" ";ПОВТОР(" ";ДЛСТР($A5)));ДЛСТР($A5)*(СТОЛБЕЦ(A:A)-1)+1;ДЛСТР($A5))))-1)
В итоге она работает, вытаскиваются первые части групп до знака "_", но только это работает когда в ячейке до 1340-1360 знаков (23-24 группы). Если количество знаков больше, то получаю ошибку - #ЗНАЧ! А у меня в среднем 1700-1800 знаков (30 групп).

Почему возникает ошибка, не пойму?
Полагаю эту задачу можно решить макросом, так даже наверно было бы правильнее, чем эта километровая формула :)

Буду безмерно благодарен за помощь!
 
 
 
#2
23.06.2019 14:07:50
Цитата
_DenA_ написал: перечисленных через запятую без пробелов
Запятая - невидимый символ? Где в она в данных?

Ага...
Цитата
Для работы формулы я заменил в оригинале разделитель запятую на пробел.
Теперь помогающим предлагаете менять обратно? Нет уж, сами формулу корректируйте.
=СЖПРОБЕЛЫ(ЛЕВБ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&$A3;" ";ПОВТОР(" ";999));СТОЛБЕЦ(A1)*999;999));"_";ПОВТОР(" ";999));999))
 
 
 
#3
23.06.2019 15:54:17
как вариант, выделяете ячейки
Данные - Текст по столбцам - С разделителями - в поле другой вписать , - Далее - Гтотво - Shift+Space - Ctrl+H
Найти: _* Заменить на: оставить пустым
Заменить все
 
 
 
#4
23.06.2019 20:44:13
Андрей Лящук, рабочий вариант! И снова Вам спасибо! :)  
 
 
 
#5
23.06.2019 20:44:50
А куда файл спрятали?
 
 
 
#6
23.06.2019 20:47:24
Цитата
vikttur написал:
А куда файл спрятали?
блииин... "добавил" файл... вообще сообщение каким то образом удалил...
 
 
 
#7
23.06.2019 20:58:43
Из-за длинных текстов. Заменить в формуле 999 на 2000, например
 
 
 
#8
23.06.2019 21:01:41
Цитата
vikttur написал:
Из-за длинных текстов. Заменить в формуле 999 на 2000, например
Пробовал, получаю в ячейке ошибку - #ЗНАЧ!
Изменено: _DenA_ - 23.06.2019 21:02:34
 
 
 
#9
23.06.2019 21:05:07
Где?
 
 
 
#10
23.06.2019 21:13:49
Доброе время суток.
Что-то странно, что тёзка Power Query не предложил.
 
 
 
#11
24.06.2019 00:53:50
Цитата
Андрей VG написал:
странно, что тёзка Power Query не предложил.
на тот момент под рукой был только Windows7 c IE8 без SP1, влом было накатывать, да и некода
 
 
 
#12
24.06.2019 08:21:47
Можно попробовать так
 
 
 
#13
24.06.2019 10:27:24
Цитата
vikttur написал:
Где?
Да собственно вот, открыл Ваш файл и вижу ту же ошибку, прицепил скрин (первый).
Я так полагаю у нас разные версии Excel и видимо есть разница в обработке формулами объемов данных. У меня Excel 2016, а у Вас?

Кстати, вот, вижу, что у Вас работает. Пока не нажимаешь "Разрешить редактирование", видно как было, стоит только нажать и все результаты работы формул преобразуется в #ЗНАЧ! На втором скрине это видно.
Изменено: _DenA_ - 24.06.2019 10:34:08
 
 
 
#14
24.06.2019 10:42:17
Подтверждаю ошибку как в сообщении #13, до того момента пока не нажму "разрешить редактирование" результат видно. как только нажимаю. ЗНАЧ.
Через вычислить формулу ошибку показывает вот в этой части
ПОДСТАВИТЬ(","&$A2;",";ПОВТОР(" ";2000)

Опытным путём удалось выяснить что ошибка лично у меня возникает если я меняю в формуле с 999 на 2000, менял с 999 на 1000 всё работает, меняю на 1001 и всё ошибка.
Изменено: Wild.Godlike - 24.06.2019 10:46:35
 
 
 
#15
24.06.2019 11:33:43
У меня Excel-2010, ошибок нет

Видимо, срабатывает ограничение по количеству знаков 32767. 30 фрагментов ---> 29*1000 = 29 000. И это только добавляемые пробелы. А еще длина текстовой строки...
Если решать такой формулой, следует разбить ее на две? каждая из которых обрабатывает по 15 столбцов.
 
 
 
#16
24.06.2019 11:43:05
Цитата
msi2102 написал:
Можно попробовать так
Хороший вариант. На небольшом объеме данных (у меня это около 20 тыс ячеек по 1750 символов в ячейке) работает отлично.
Но когда пытаюсь обработать сразу 50-800 тыс строк вылетает ошибка переполнения - Run-time error '6': Overflow
 
 
 
#17
24.06.2019 12:03:59
Цитата
vikttur написал:
У меня Excel-2010, ошибок нет
Хм, тогда не пойму, а почему у Вас ошибок нет?
Поставить что ли тоже тогда 2010-й... Я думал наоборот, посвежее версия, Excel более продуктивный... например, в 2019 вроде снято ограничение на длину элемента массива в 255 символов, выяснилось опытным путем при сравнении на 2016-й и 2019-й версиях...
 
 
 
#18
24.06.2019 15:32:24
Попробуйте так.
Обратите внимание если между запятыми отсутствует "_" в ячейку выводится предупреждение "После запятой отсутствует "_"". Если нужно просто значение целиком (от запятой до запятой), тогда в коде закомментируйте  строку с предупреждением и разкоментируйте нижнюю строку  
Изменено: msi2102 - 24.06.2019 15:47:28
 
 
 
Страницы: 1
Наверх