Содержание:
Разрывы строк внутри одной ячейки, добавленные с помощью сочетания клавиш. другой+Enter это очень распространенная и распространенная вещь. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо работающих программ (привет 1С, SAP и т.п.). Проблема в том, что тогда приходится не только любоваться такими таблицами, но и работать с ними – и тогда эти невидимые переносы символов могут оказаться проблема. А могут и не стать – если знать, как с ними правильно обращаться.
Давайте рассмотрим этот вопрос более подробно.
Удаление разрывов строк путем замены
Если нам нужно избавиться от дефисов, то первое, что обычно приходит на ум, — это классический прием «найти и заменить». Выделите текст, а затем вызовите окно замены с помощью сочетания клавиш. Ctrl+H или через Главная – Найти и выбрать – Заменить (Главная страница — Найти и выбрать — Заменить). Одно несоответствие — не очень понятно как вводить в верхнее поле Найти (Найти то, что) наш невидимый символ разрыва строки. другой+Enter здесь, к сожалению, это уже не работает, скопировать этот символ прямо из ячейки и вставить сюда тоже не получится.
Комбинация поможет Ctrl+J - это альтернатива другой+Enter в диалоговых окнах Excel или полях ввода:
Обратите внимание, что после того, как вы поместите мигающий курсор в верхнее поле и нажмите Ctrl+J – в самом поле ничего не появится. Не пугайтесь – это нормально, символ невидим 🙂
В нижнее поле Замена (Заменить) либо ничего не вводить, либо вводить пробел (если мы хотим не просто убрать дефисы, а заменить их пробелом, чтобы строки не склеивались в единое целое). Просто нажмите кнопку Замени все (Заменить все) и наши дефисы исчезнут:
Нюанс: после выполнения замены, введенной с помощью Ctrl+J невидимый персонаж остается в поле Найти и может мешать в будущем – не забудьте удалить его, поместив курсор в это поле и несколько раз (для надежности) нажав клавиши Удалить и Возврат на одну позицию.
Удаление разрывов строк с помощью формулы
Если вам нужно решить задачу с формулами, то вы можете воспользоваться встроенной функцией ПЕЧАТЬ (ЧИСТЫЙ), который может очистить текст от всех непечатаемых символов, включая наши злополучные разрывы строк:
Этот вариант, однако, не всегда удобен, поскольку линии после этой операции можно склеить. Чтобы этого не произошло, нужно не просто убрать дефис, а заменить его пробелом (см. следующий пункт).
Замена разрывов строк формулой
А если вы хотите не просто удалить, а заменить другой+Enter на, например, пробеле, то потребуется другая, чуть более сложная конструкция:
Чтобы установить невидимый дефис, мы используем функцию SYMBOL (СИМВОЛ), который выводит символ по его коду (10). И тогда функция ЗАМЕНА (ЗАМЕНЯТЬ) ищет в исходных данных наши дефисы и заменяет их любым другим текстом, например, пробелом.
Деление на столбцы переносом строки
Знакомый многим и очень удобный инструмент Текст по столбцам на вкладке Данные (Данные — текст в столбцы) также может отлично работать с разрывами строк и разбивать текст из одной ячейки на несколько, разбивая его по другой+Enter. Для этого на втором шаге мастера необходимо выбрать вариант произвольного символа-разделителя. Другое (Обычай) и используйте уже знакомое нам сочетание клавиш Ctrl+J как альтернатива другой+Enter:
Если ваши данные могут содержать несколько разрывов строк подряд, то вы можете «свернуть» их, включив флажок Рассматривать последовательные разделители как один (Считать последовательные разделители как один).
После нажатия на Следующая (Следующий) и пройдя все три шага мастера, получаем желаемый результат:
Обратите внимание, что перед выполнением этой операции необходимо справа от разбитого столбца вставить достаточное количество пустых столбцов, чтобы полученный текст не перезаписал значения (цены), которые были справа.
Разделите на строки с помощью Alt + Enter через Power Query.
Еще одна интересная задача — разделить многострочный текст из каждой ячейки не на столбцы, а на строки:
Вручную это делать долго, с формулами сложно, макрос написать не каждый сможет. Но на практике эта проблема возникает чаще, чем хотелось бы. Самое простое и легкое решение — использовать для этой задачи надстройку Power Query, которая встроена в Excel с 2016 года, а для более ранних версий 2010-2013 годов ее можно скачать совершенно бесплатно с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query, необходимо сначала преобразовать их в «умную таблицу» с помощью сочетания клавиш. Ctrl+T или по кнопке Форматировать как таблицу таб Главная (Главная страница — Форматировать как таблицу). Если по каким-то причинам вы не хотите или не можете использовать «умные таблицы», то можно работать с «тупыми». В этом случае просто выберите исходный диапазон и дайте ему имя на вкладке. Формулы – Менеджер имен – Новое (Формулы — Менеджер имен — Новое).
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) вы можете нажать на кнопку Из таблицы/диапазона (Из таблицы/диапазона)чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделите столбец с многострочным текстом в ячейках и выберите команду на вкладке «Основные». Разделить столбец – по разделителю (Главная страница — Разделить столбец — По разделителю):
Скорее всего, Power Query автоматически распознает принцип деления и подставит сам символ #(лф) невидимый символ перевода строки (lf = перевод строки = перевод строки) в поле ввода разделителя. При необходимости другие символы можно выбрать из выпадающего списка внизу окна, если предварительно поставить галочку Разделить специальными символами (Разделено специальными символами).
Чтобы все было разбито на строки, а не на столбцы – не забудьте переключить селектор Ряды (по рядам) в группе дополнительных параметров.
Остается только нажать OK и получите то, что хотите:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить – Закрыть и загрузить… таб Главная (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…).
Важно отметить, что при использовании Power Query необходимо помнить, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления необходимо щелкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить и сохранить (Обновить) или нажмите кнопку Обновить все таб Данные (Данные — Обновить все).
Макрос для разделения на строки по Alt+Enter
Для полноты картины упомянем еще решение предыдущей задачи с помощью макроса. Откройте редактор Visual Basic с помощью одноименной кнопки на вкладке Застройщик (Разработчик) или сочетания клавиш другой+F11. В появившемся окне вставьте новый модуль через меню Вставка – Модуль и скопируйте туда следующий код:
Sub Split_By_Rows() Тусклая ячейка As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'определить количество фрагментов cell.Offset(1, 0) ).Resize(n, 1).EntireRow.Insert 'вставляем пустые строки ниже ячейки.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'вводим в них данные из массива Set cell = cell.Offset(n + 1, 0) 'перейти к следующей ячейке Next i End Sub
Вернитесь в Excel и выделите ячейки с многострочным текстом, который вы хотите разделить. Затем используйте кнопку Макрос таб разработчик (Разработчик — Макросы) или сочетание клавиш другой+F8для запуска созданного макроса, который сделает всю работу за вас:
Вуаля! Программисты, по сути, просто очень ленивые люди, которые предпочитают один раз поработать, а потом ничего не делать 🙂
- Очистка текста от мусора и лишних символов
- Замена текста и удаление неразрывных пробелов с помощью функции ПОДСТАВИТЬ
- Как разделить липкий текст на части в Excel