Как распарсить строку в excel
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее.
Удаление переносов строк заменой
Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:
Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .
Удаление переносов строк формулой
Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:
Такой вариант, однако, не всегда бывает удобен, т.к. строки после этой операции могут склеиваться между собой. Чтобы этого не происходило, нужно не просто удалять символ переноса, а заменять его на пробел (см. следующий пункт).
Замена переносов строк формулой
А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:
Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.
Деление на столбцы по переносу строки
Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :
Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .
После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:
Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.
Деление на строки по Alt+Enter через Power Query
Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:
Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :
Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.
Останется только нажать на ОК и получить желаемое:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .
Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Макрос для деления на строки по Alt+Enter
Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)
Необходимо по особенному распарсить таблицу. Что только не пробовал (разделитель по запятым в том числе). Это необходимо для дальнейшей работы в PowerBI. Массив данных очень большой, просто руками не сделать
Буду очень благодарен за идеи!
Необходимо по особенному распарсить таблицу. Что только не пробовал (разделитель по запятым в том числе). Это необходимо для дальнейшей работы в PowerBI. Массив данных очень большой, просто руками не сделать
Буду очень благодарен за идеи! AngelOfLegend
Буду очень благодарен за идеи! Автор - AngelOfLegend
Дата добавления - 07.10.2016 в 19:43
AngelOfLegend, приложите пример в файле.
Характеристики всегда разделены запятыми или могут быть другие варианты?
AngelOfLegend, приложите пример в файле.
Характеристики всегда разделены запятыми или могут быть другие варианты? Manyasha
ЯД: 410013299366744 WM: R193491431804
Характеристики всегда разделены запятыми или могут быть другие варианты? Автор - Manyasha
Дата добавления - 07.10.2016 в 19:47
Дата добавления - 07.10.2016 в 20:10 Вариант с макросом. После работы макроса будет создан новый лист.
[vba] Вариант с макросом. После работы макроса будет создан новый лист.
[vba] [/vba] Автор - Karataev
Дата добавления - 07.10.2016 в 20:11 БЛАГОДАРЮ! Я ДИКО ДОВОЛЕН! ))) Сейчас буду пробовать на рабочем варианте )))) БЛАГОДАРЮ! Я ДИКО ДОВОЛЕН! ))) Сейчас буду пробовать на рабочем варианте )))) AngelOfLegend
AngelOfLegend, вопросы по своей теме задавайте здесь, не нужно в личку писать.
Для того, чтобы перенести макрос в другой файл, нажмите alt+f11 (или вкладка Разработчик-Visual Basic) - Вы в редакторе VBA. В модуле module1 есть макрос test. Его нужно скопировать в модуль рабочего файла. Для этого откройте редактор VBA рабочего файла - щелкните правой кнопкой мыши (пкм) по VB-проекту книги (см. скрин)
нажмите insert-module - 2-м щелчком откройте созданный модуль - вставьте код макроса.
Или вариант попроще: модуль с макросом (все там же, в редакторе VBA) можно перетащить мышкой из одного VB-проекта в другой.
Чтобы привязать макрос к кнопке, щелкаете пкм по кнопке-назначить макрос-выбираете test (ну или как Вы его там назовете).
[p.s.]И вариант Karataevа посмотрите, он тоже старался![/p.s.]
AngelOfLegend, вопросы по своей теме задавайте здесь, не нужно в личку писать.
Для того, чтобы перенести макрос в другой файл, нажмите alt+f11 (или вкладка Разработчик-Visual Basic) - Вы в редакторе VBA. В модуле module1 есть макрос test. Его нужно скопировать в модуль рабочего файла. Для этого откройте редактор VBA рабочего файла - щелкните правой кнопкой мыши (пкм) по VB-проекту книги (см. скрин)
нажмите insert-module - 2-м щелчком откройте созданный модуль - вставьте код макроса.
Или вариант попроще: модуль с макросом (все там же, в редакторе VBA) можно перетащить мышкой из одного VB-проекта в другой.
Чтобы привязать макрос к кнопке, щелкаете пкм по кнопке-назначить макрос-выбираете test (ну или как Вы его там назовете).
[p.s.]И вариант Karataevа посмотрите, он тоже старался![/p.s.] Manyasha
Для того, чтобы перенести макрос в другой файл, нажмите alt+f11 (или вкладка Разработчик-Visual Basic) - Вы в редакторе VBA. В модуле module1 есть макрос test. Его нужно скопировать в модуль рабочего файла. Для этого откройте редактор VBA рабочего файла - щелкните правой кнопкой мыши (пкм) по VB-проекту книги (см. скрин)
нажмите insert-module - 2-м щелчком откройте созданный модуль - вставьте код макроса.
Или вариант попроще: модуль с макросом (все там же, в редакторе VBA) можно перетащить мышкой из одного VB-проекта в другой.
Чтобы привязать макрос к кнопке, щелкаете пкм по кнопке-назначить макрос-выбираете test (ну или как Вы его там назовете).
Столкнулся с такой проблемой.
Есть таблица excel, сократим ее до 2-х столбцов:
- Запрос
- Потраченное время
В запросе хранится цифровой номер, например, 555. В потраченном времени, соответственно, время, которое потратил человек на выполнение запроса.
Но в таблице появилось много брака, в частности в столбце Запрос. Начали включать в него несколько номеров через запятую и, соответственно, указывают общее время на выполнение, т.е.:
555, 444, 111 с потраченным временем 9 часов
Можно ли как-то разделить такие строки на несколько (в зависимости от кол-ва номеров в столбце), а затраченное время высчитывать также исходя из кол-ва?
Т.е. на выходе получить:
555 - 3 часа
444 - 3 часа
111 - 3 часа
Столкнулся с такой проблемой.
Есть таблица excel, сократим ее до 2-х столбцов:
- Запрос
- Потраченное время
В запросе хранится цифровой номер, например, 555. В потраченном времени, соответственно, время, которое потратил человек на выполнение запроса.
Но в таблице появилось много брака, в частности в столбце Запрос. Начали включать в него несколько номеров через запятую и, соответственно, указывают общее время на выполнение, т.е.:
555, 444, 111 с потраченным временем 9 часов
Можно ли как-то разделить такие строки на несколько (в зависимости от кол-ва номеров в столбце), а затраченное время высчитывать также исходя из кол-ва?
Т.е. на выходе получить:
555 - 3 часа
444 - 3 часа
111 - 3 часа donarny
Столкнулся с такой проблемой.
Есть таблица excel, сократим ее до 2-х столбцов:
- Запрос
- Потраченное время
В запросе хранится цифровой номер, например, 555. В потраченном времени, соответственно, время, которое потратил человек на выполнение запроса.
Но в таблице появилось много брака, в частности в столбце Запрос. Начали включать в него несколько номеров через запятую и, соответственно, указывают общее время на выполнение, т.е.:
555, 444, 111 с потраченным временем 9 часов
Можно ли как-то разделить такие строки на несколько (в зависимости от кол-ва номеров в столбце), а затраченное время высчитывать также исходя из кол-ва?
Т.е. на выходе получить:
555 - 3 часа
444 - 3 часа
111 - 3 часа Автор - donarny
Дата добавления - 27.03.2017 в 22:45
Здравствуйте, подскажите пожалуйста, как можно решить такую задачу:
В таблице Excel есть столбец со строками вида:
«ЭРТ №2 Такой-то край, Такой-то район, в/г ВоенныйГородок № 42 Кот 174 Здание контрольно-пропускного пункта (караулка)(встроенная) №174» без кавычек.
Необходимо в три соседних ячейки той же строки, выдернуть числа после первого, второго и третьего символа «№» соответственно.
Учитывая, что между № и цифрой может быть или не быть пробел.
Я так понимаю, необходимо 3 формулы, но никак не получается сообразить, как получить число неизвестной длинны после символа.
Excel 7-10 года, Windows.
__________________Помощь в написании контрольных, курсовых и дипломных работ здесь
Поиск значения элемента(символа) строки по расположению в массиве символов
Задача такова: есть некий массив(псевдо-база_данных) символов, где хранятся символы русского.
Простой парсинг строки
Привет всем ! :) У меня возникла сложная такая штука: есть строка, которая по своей сути задает.
Парсинг значения строковой переменной с игнорированием символа
Такая задача - имеется строка, нужно парсить её (у меня парсинг с конца строки планируется, если.
Простой парсинг XML строки средствами php
Добрый вечер коллеги, помогите решить задачку. Имеется срока $string = "<order .
Решение
Скрипт верно, но я пробовал сделать это с помощью формул, получается страшная жесть, вроде:с помощью vba же, это сделать оказалось значительно проще, аккуратней и красивей
В принципе можно и формулой одной для всех:
=ПОДСТАВИТЬ(ПРАВСИМВ(ЛЕВСИМВ("№"&ПОДСТАВИТЬ($A$1;"№";ПОВТОР( "№";999));999*СТРОКА(A2));999);"№";"")
СТРОКА(A2) - это номер вхождения. Если вытягиваете не по строкам, а по столбцам, то замените на СТОЛБЕЦ(B1)
Вводить как формулу массива, выделив 3 соседние ячейки и нажимая Ctrl+Shift+Enter.Из строки сформируйте новую, добавив заданный пользователем символ после каждого символа строки
Строки на Java!помогите в тупике Из заданной пользователем строки сформируйте новую, добавив.
Отсечь часть строки после вхождения символа/строки
string file = File.ReadAstring _file = File.ReadAllLines(_fileName); Такое вхождение.
Строки. Вставить после 3 символа в данной строке, подстроку «rama» и посчитать длину получившейся строки
Пользователем задана строка. Вставить после 3 символа в данной строке, подстроку «rama» и посчитать.
Читайте также: