Тонкости работы с переносами строк в 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:

Деление на столбцы по Alt+Enter

Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one).

После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:

Результат деления

Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.

Деление на строки по Alt+Enter через Power Query

Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:

Деление на строки по Alt+Enter

Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки 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:

Грузим нашу таблицу в Power Query

После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter):

Деление столбца на строки в Power Query

Скорее всего, Power Query автоматически распознает принцип деления и сам подставит условное обозначение #(lf) невидимого символа переноса строки (lf = line feed = перенос строки) в поле ввода разделителя. Если нужно, то другие символы можно выбрать из выпадающего списка в нижней части окна, если включить предварительно галочку Разделить с помощью специальных символов (Split by special characters).

Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.

Останется только нажать на ОК и получить желаемое:

Результаты

Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в... на вкладке Главная (Home - Close&Load - Close&Load to...).

Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All).

Макрос для деления на строки по Alt+Enter

Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt+F11. В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:

Sub Split_By_Rows()
    Dim cell As Range, n As Integer

    Set cell = ActiveCell

    For i = 1 To Selection.Rows.Count
        ar = Split(cell, Chr(10))         'делим текст по переносам в массив
        n = UBound(ar)                    'определяем кол-во фрагментов
        cell.Offset(1, 0).Resize(n, 1).EntireRow.Insert             'вставляем пустые строки ниже
        cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar)     'вводим в них данные из массива
        Set cell = cell.Offset(n + 1, 0)                            'сдвигаемся на следующую ячейку
    Next i
End Sub

Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8, чтобы запустить созданный макрос, который и проделает за вас всю работу:

Работа макроса разделения на строки по Alt+Enter

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

Ссылки по теме



17.04.2019 08:21:45
Sub RemoveCarriageReturns()
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    For Each MyRange In ActiveSheet.UsedRange
        If 0 < InStr(MyRange, Chr(10)) Then
            MyRange = Replace(MyRange, Chr(10), "")
        End If
    Next
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
Макрос для удаления переносов строк.
19.04.2019 07:43:08
Николай, большое спасибо за статью!
19.04.2019 10:16:31
Линк на .xlxs файл с разнесением данных по строкам с использованием штатных фун-ций EXCEL (к задаче "Деление на строки по Alt+Enter через Power Query"). :)


Ссылка(Я диск).
17.05.2019 10:21:17
А не подскажете, есть ли сочетание клавиш, с помощью которого можно вставить в диалога замены символ с кодом 160? Типа как Ctrl+J для переноса строки.
07.06.2019 09:55:17
Alt+0160
Зажимаете Alt, затем на цифровой клавиатуре поочередно вводите нужные цифры: 0-1-6-0

Это коды ASCII. Например, Ctrl+J можно заменить на Alt+0010
17.05.2019 21:32:55
Ещё подскажите, может кто сталкивался: открыл пример "Тонкости работы с переносами строк", через найти-заменить проделал, потом вернулся назад на одно действие, снова пытаюсь найти "Ctrl-J" и заменить на "_" - пишет что ничего не найдено...
26.08.2019 18:48:55
Спасибо за полезный урок! Скажите пожалуйста, а если в столбце будут значения как с наличием разделителя "Alt+Enter", так и без него, как тогда будет выглядеть макрос. Например, мне нужно распарсить значения из ячейки с разделителем Alt+Enter в строки, но попадаются ячейки без Alt+Enter, сейчас макрос выдает ошибку? Спасибо!
19.09.2019 11:27:37
Добрый день.
Подскажите, как можно привязать информацию при разбивке к новой строке?
В Вашем последнем примере (макрос), при разбивке на строки сумм, имена менеджеров остаются только в первой строке. Можно ли их привязать к суммам и при разбивке ячейки на строки они будут подвязаны к новой строке?
Иван 54122
Иван 23213
Иван 5540 итд.
20.09.2019 16:31:09
Есть способ хотя он не всегда помогает. Все упирается в новую строку. Но там вообщем несложно немного переделать именно ввод


С помощью параметров разбивки на страницы можно определить, куда будут вставляться автоматические разрыв страницы, или переопределить их.

Важно: Описанные ниже процедуры должны выполняться в режиме разметки. Если используется другой режим, в меню Вид выберите пункт Разметка страницы.


Выполните одно из указанных ниже действий.
Не разрывать абзац
Можно сделать так, чтобы все строки абзаца находились на одной странице или в одной колонке. Благодаря этому абзац не будет разрываться между двумя страницами.

  1. Выделите строки, которые должны находиться вместе.

  2. В меню Формат выберите пункт Абзац и откройте вкладку Положение на странице.

  3. Установите флажок Не разрывать абзац.
Не отрывать от следующего
Можно сделать так, чтобы абзацы, например заголовок и последующий абзац, выводились на одной странице или в одной колонке.

  1. Выделите абзацы, которые нужно расположить на одной странице.

  2. В меню Формат выберите пункт Абзац и откройте вкладку Положение на странице.

  3. Установите флажок Не отрывать от следующего.
Принудительное добавление разрыва страницы перед абзацем
Если нужно, чтобы абзац всегда выводился вверху страницы, добавьте перед ним разрыв страницы.

  1. Выделите абзац, перед которым нужно вставить разрыв страницы.

  2. В меню Формат выберите пункт Абзац и откройте вкладку Положение на странице.

  3. Установите флажок С новой страницы.
У нас в офисе мегаиндекс часто приходится обновлять софт, ввиду новых поисковых алгоритмов. Но без настроек не всегда получается полноценная работа. Но толковый сисадмин в офисе - это залог успеха.

И еще с этой функцией не всегда лады бывают. Но решается просто



Запрет висячих строк
По умолчанию в Word последняя строка абзаца не может выводиться вверху или внизу страницы.

  1. Выделите абзацы, в которых нужно запретить висячая строка.

  2. В меню Формат выберите пункт Абзац и откройте вкладку Положение на странице.

  3. Установите флажок Запрет висячих строк.
Запрет переноса строк таблицы на следующую страницу

  1. Щелкните таблицу.

  2. В меню Таблица выберите команду Свойства таблицы, а затем откройте вкладку Строка.

  3. Снимите флажок Разрешить перенос строк на следующую страницу.
Здравствуйте!
Большое спасибо за очень подробную статью. В большинстве таблиц очень помогло, но возникла проблема с обработкой в PowerQuerry данных из 1С.
В рамках одной таблицы, когда один столбец разделял на несколько по пользовательскому критерию #(lf), то в отношении одного из столбцов данный подход не сработал. В ячейках этого проблемного столбца все строчки с переносами, но никакими знаками данные переносы почем у-то не идентифицируются.

Вы случайно не знакомы с данной проблемой? Это может еще какой-то способ переноса, кроме  #(lf)?
08.04.2021 07:44:01
Столкнулся с аналогичной проблемой, данные выгружены из БД в самом Excel заменить переносы могу, но автоматизировать с помощью Power Querry не выходит, перепробовал все варианты, но увы...
Ни кто не нашел  ответа на данный вопрос?
22.08.2020 22:17:17
Здравствуйте, в макросе есть одна недороботка, а именно:
Например имеется 5 ячеек с данными. в первых двух имеется перенос, в третьей нету переноса в 4 и 5 снова перенос. При выделении всех 5 ячеек макрос выдает ошибку и делает работу до 3 ячейки. Тоесть он прерывается если нету переноса.  можно это как то обойти ?
16.10.2020 05:13:51
Здравствуйте, как будет выглядеть макрос, чтобы данные из ячейки с разделителем ";" перенести в другой столбец с проверкой данных в ячейке и если она не пуста, то найти пустую ячейку в этом столбце и записать данные, которые мы делили ранее?
08.07.2021 11:47:46
Добрый день.
Подскажите как через VBA удалять только последний Символ(10)?
23.05.2022 14:59:06
Уважаемый Николай!
Как всегда огромное спасибо за шикарный урок для малограмотных типа меня!
Подскажите пожалуйста как можно решать задачу наоборот, сложить слова с переводом строки?
Был ли у Вас такой ролик или чонить подобное?
Еще раз огромное спасибо за Ваши праведные труды!
16.02.2023 14:23:07
Спасибо Вам огромное! всегда спасают ваши публикации.
Добрый день!
Не могу победить этот перенос строки в power query. Он упорно не хочет его видеть, плюс почему-то меню разделения по столбцам у меня не такое расширенное, как у автора и предлагает в пользовательском разделителе только кол-во столбцов. Просто замена символа на #(lf) на другой, тоже ни к чему не привела.
В обычной умной таблице все получается легко, но хотелось форматировать выгрузку полностью в query/
Подскажите что не так. Спасибо

Проблему с расширенными настройками решил, обновил эксель. После этого сразу все заработало.
21.01.2024 13:03:19
Подскажите, как сделать возврат каретки массово (пакетно) на куче файлов CSV?
Наверх