Процедуры из статьи доступны в !SEMTools Lite и Pro.
Удалить переносы строк в Excel чаще всего нужно после копирования: адрес из письма, описание товара из CRM или выгрузка из 1С приходят в ячейку в несколько строчек, и таблица распухает, а фильтр и ВПР перестают находить совпадения. Хочется, чтобы текст снова стоял в одну строку, а слова не слиплись. Быстрее всего это делает «Найти и заменить»: в поле «Найти» нажмите Ctrl+J, в «Заменить на» поставьте пробел. Формулой то же самое даёт =ПОДСТАВИТЬ(A2;СИМВОЛ(10);" "), а убрать переносы сразу во всём выделении, вместе с невидимым «возвратом каретки», поможет процедура замены по шаблону.
Что такое перенос строки в ячейке
Перенос строки (Энтер внутри ячейки, Alt+Enter) это невидимый символ с кодом 10, по-английски line feed (LF). В данных из CSV, 1С и с сайтов перед ним часто стоит ещё символ 13, «возврат каретки» (CR). Его не видно, но он мешает сравнению и не удаляется вместе с СИМВОЛ(10). Как переносы ставят и зачем, рассказано в статье перенос строки в ячейке Excel.
Убрать переносы через «Найти и заменить»
- Выделите диапазон (или ничего не выделяйте, чтобы заменить на всём листе).
- Нажмите Ctrl+H.
- В поле «Найти» нажмите Ctrl+J. Поле будет выглядеть пустым, иногда мигает точка: символ там есть.
- В поле «Заменить на» поставьте пробел, запятую с пробелом или оставьте пустым.
- Нажмите «Заменить все».
Если поле «Заменить на» оставить пустым, слова на стыке строк слипнутся: «Москва» и «ул. Тверская» станут «Москваул. Тверская». Пробел почти всегда безопаснее. Символ 13 так не найти, его убирают формулой или процедурой ниже.
Удалить переносы строк формулой
| A | B | C | |
|---|---|---|---|
| 1 | Исходный текст | Способ | Результат |
| 2 | Москва ул. Тверская д. 7 | ПОДСТАВИТЬ на пробел | Москва ул. Тверская д. 7=ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ") |
| 3 | Москва ул. Тверская д. 7 | ПОДСТАВИТЬ на запятую | Москва, ул. Тверская, д. 7=ПОДСТАВИТЬ(A3;СИМВОЛ(10);", ") |
| 4 | Москва ул. Тверская д. 7 | ПЕЧСИМВ | Москваул. Тверскаяд. 7=ПЕЧСИМВ(A4) |
ПОДСТАВИТЬ меняет каждый перенос на то, что указано в третьем аргументе.
ПЕЧСИМВ удаляет все непечатаемые символы, в том числе 10 и 13, но ничего не ставит вместо них, и слова склеиваются.
В английском Excel: =SUBSTITUTE(A2,CHAR(10)," ")
Для данных из CSV и 1С уберите сначала символ 13, затем 10, а СЖПРОБЕЛЫ схлопнет двойные пробелы, если переносов было несколько подряд:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(13);"");СИМВОЛ(10);" "))
Формулу протягивают вниз, а затем заменяют результаты значениями и переносят их на место исходного столбца.
Удалить переносы во всём диапазоне процедурой
Формула требует вспомогательного столбца, а «Найти и заменить» не видит символ 13. В !SEMTools переносы заменяются на месте регулярным выражением, которое ловит оба символа сразу:
- Выделите диапазон.
- «ИЗМЕНИТЬ» → «Заменить» → «По RegEx» → «Все вхождения».
- В окне «Паттерн» введите
\r?\n: необязательный возврат каретки и перенос строки. - В окне «Заменить на» введите пробел или «, » и нажмите OK.
Заменяются все вхождения в каждой ячейке, значения записываются прямо в выделение. Чтобы удалить переносы без замены, используйте «УДАЛИТЬ» → «Символы» → «по REGEX» → «Все, попадающее под паттерн» с тем же шаблоном, но помните, что слова на стыке склеятся. Шаблон (\r?\n)+ заменит серию переносов подряд одним пробелом. О синтаксисе шаблонов подробно в статье регулярные выражения в Excel.
Убрать не все переносы, а только лишние
Иногда строки в ячейке нужны, а мешают только пустые строки между ними или одинаковые строки. Это отдельные задачи, и для них есть свои процедуры:
- Пустые строки внутри ячейки (два переноса подряд, переносы в начале и в конце): удалить пустые строки в ячейке.
- Повторяющиеся строки внутри ячейки: удалить повторы строк в ячейке.
- Не удалять, а разнести каждую строку ячейки в отдельную строку таблицы: разделить ячейку на строки.
Выключить перенос текста
Если в ячейке нет символов переноса, а текст всё равно идёт в несколько строк, это автоперенос по ширине столбца. Выделите ячейки и отожмите кнопку «Перенос текста» на вкладке «Главная». Текст встанет в одну строку и уйдёт за край ячейки, если справа пусто. Высоту строки после этого подгоните двойным щелчком по её нижней границе.
Почему переносы не удаляются
- «Найти и заменить» пишет, что ничего не найдено. В поле «Найти» нет символа: нажмите Ctrl+J ещё раз, предварительно очистив поле.
- После замены текст по-прежнему в две строки. В ячейке остался символ 13 (возврат каретки) или это автоперенос. Проверьте формулой =ЕЧИСЛО(ПОИСК(СИМВОЛ(13);A2)) или отожмите «Перенос текста».
- Слова склеились. Переносы заменили на пустоту или использовали ПЕЧСИМВ. Заменяйте на пробел.
- Между словами по два пробела. Переносов было несколько подряд: оберните формулу в СЖПРОБЕЛЫ или используйте шаблон (\r?\n)+. О пробелах подробно в статье лишние пробелы в ячейках.
- Строка листа осталась высокой. Высота не пересчиталась сама: двойной щелчок по нижней границе номера строки.
Удалить переносы строк: частые вопросы
Как убрать перенос строки в ячейке Excel?
Через Ctrl+H: в поле «Найти» нажмите Ctrl+J, в «Заменить на» поставьте пробел и нажмите «Заменить все».
Как заменить перенос строки на пробел формулой?
=ПОДСТАВИТЬ(A2;СИМВОЛ(10);" "), для данных из CSV добавьте замену СИМВОЛ(13) на пустоту.
Как заменить перенос строки на запятую?
=ПОДСТАВИТЬ(A2;СИМВОЛ(10);", ") или в «Найти и заменить» указать «, » в поле «Заменить на».
Что такое возврат каретки в Excel?
Невидимый символ с кодом 13 (CR), который в файлах из других программ стоит перед переносом строки. Его убирает =ПОДСТАВИТЬ(A2;СИМВОЛ(13);"").
Как убрать перенос текста в ячейке?
Если это автоперенос, отожмите кнопку «Перенос текста» на вкладке «Главная». Если в тексте есть символы переноса, замените их на пробел.
Выгрузки из CRM и 1С с переносами, возвратами каретки и пустыми строками в ячейках: !SEMTools чистит их в выделении на месте, регулярным выражением или готовыми процедурами для строк внутри ячеек.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить переносы строк в Excel
Разово переносы убирает Ctrl+H с Ctrl+J в поле «Найти», в формуле их меняет ПОДСТАВИТЬ с СИМВОЛ(10), а с данными из CSV и 1С надёжнее процедура замены по шаблону \r?\n. Если мешают только пустые или повторяющиеся строки, используйте процедуры для пустых строк и повторов строк в ячейке. Другие способы очистки текста собраны в разделе удалить символы.