Как убрать переносы строк в ячейке Excel
А вы знаете как убрать переносы строк внутри ячейки Excel?
Расскажу вам о трёх способах.
Способ №1
Если ячеек с переносами строк мало, то просто удалите символы или в конце, или в начале строки внутри ячейки с помощью клавиш Backspace или Delete.
Если ячеек с переносами много, то вам подойдут способы №2 и №3.
Способ №2
Для начала определите: есть ли в конце каждой строки ячейки пробел?
Для этого щелкните дважды левой клавишей мыши по ячейке и выделите крайний символ в строке.
Если символ выделяется – пробел есть.
Если есть пробел в конце строки.
▪️ Нажмите сочетание клавиш CTRL+H
▪️ В поле Найти нажмите сочетание клавиш CTRL+J для вставки символа переноса
▪️ Поле Заменить на оставьте пустым
▪️ Щелкните Заменить все и Закрыть
Если НЕТ пробела в конце строки:
▪️ Нажмите сочетание клавиш CTRL+H
▪️ В поле Найти нажмите сочетание клавиш CTRL+J для вставки символа переноса
▪️ В поле Заменить на поставьте пробел
▪️ Щелкните Заменить все и Закрыть
Способ №3
В соседней ячейке примените функцию ПЕЧСИМВ (англ. CLEAN) для удаления непечатаемых символов.
Все три способа успешно уберут переносы ��
Поделитесь, пожалуйста, в комментариях, какой способ вам понравился больше?
Тонкости работы с переносами строк в 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) :
Скорее всего, 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 и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты — это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать 🙂
Как удалить разрывы строк в Excel (3 простых способа)
Если вы работаете с данными, импортированными из баз данных или отдела продаж, вы, вероятно, уже имели дело с переносами строк.
Разрыв строки — это то, что позволяет вам иметь несколько строк в одной ячейке в Excel.
Ниже приведен пример набора данных имени и адреса, в котором одна строка, имя и различные части адреса разделены разрывом строки.
Хотя в приведенном выше примере это выглядит великолепно, иногда вам могут не понадобиться эти разрывы строк и вам необходимо удалить эти разрывы строк из набора данных.
В этом уроке я покажу вам три простых способа удалить разрывы строк в Excel (также называемый внутрикамерным возврат каретки в Excel).
Удаление разрывов строк с помощью функции поиска и замены
Самый простой способ удалить разрывы строк вручную — использовать «Найти и заменить».
Вы можете легко применить это к большому набору данных, а функция «Найти и заменить» сделает всю тяжелую работу, найдя все разрывы строк и удалив их из ячеек.
Предположим, у вас есть набор данных адресов, как показано ниже, из которого вы хотите удалить разрывы строк.
Ниже приведены инструкции по удалению разрывов строк с помощью функции «Найти и заменить» и замене их запятой:
- Выберите набор данных, из которого вы хотите удалить разрывы строк
- Перейдите на вкладку «Главная»
- В группе редактирования нажмите «Найти и выбрать».
- В появившихся вариантах нажмите «Заменить».
- Поместите курсор в поле «Найти что» и используйте сочетание клавиш — Ctrl + J (удерживая клавишу Ctrl, нажмите клавишу J). Вы можете ничего не видеть, но это помещает символ разрыва строки в поле «Найти».
- В поле замены введите запятую, за которой следует пробел (,)
- Нажмите «Заменить все».
Приведенные выше шаги удаляют все разрывы строк и заменяют их запятыми. Это даст вам результат в одной строке в ячейке Excel.
Если вы хотите удалить разрыв строки и заменить его чем-то другим вместо запятой, вам необходимо указать это на шаге 6 (в поле «Заменить на»).
Примечание. Рекомендуется создать резервную копию файла с данными, чтобы не потерять их в случае, если они понадобятся в будущем.
Удаление разрывов строк с помощью формулы
Еще один способ избавиться от разрывов строк в Excel — использовать формулу. Хотя метод «Найти и заменить» дает статические результаты, формула выдаст результаты, которые будут автоматически обновляться, если вы внесете какие-либо изменения в исходный набор данных.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите удалить разрывы строк во всех ячейках.
Ниже приведена формула, которая сделает это:
= ПОДСТАВИТЬ (A2; СИМВОЛ (10); «»)
Функция SUBSTITUTE находит и заменяет символ CHAR (10), который представляет символ перевода строки. Таким образом, приведенная выше формула находит все разрывы строк и заменяет их пробелами, что по сути означает, что эти разрывы строк были удалены.
Если вы хотите, чтобы результат был разделен запятой (или любым другим символом), вы можете использовать следующую формулу:
= ПОДСТАВИТЬ (A2; СИМВОЛ (10); «;»)
Примечание. Если у вас включен режим переноса текста в ячейке, в которой вы получаете результат формулы, вы все равно можете увидеть, как какой-то текст перемещается на следующую строку в той же ячейке. Это не из-за разрывов строк. Если вы отрегулируете столбец
Удаление разрывов строк с помощью VBA
Если вам удобно использовать VBA, это может быть самым быстрым способом избавиться от разрывов строк в Excel.
Но вам нужно поработать, прежде чем выполнять операцию в один клик.
Ниже приведен код VBA, который будет проходить через каждую ячейку в
Sub RemoveLineBreaks () для каждой ячейки в выделении Cell.Value = Replace (Cell.Value, Chr (10), «,») Next End Sub
В приведенном выше коде используется функция REPLACE в VBA для замены всех символов разрыва строки в ячейке запятой, за которой следует символ пробела.
Результат этого кода VBA является статическим, и вы не можете его отменить. Итак, если вы планируете использовать его, я настоятельно рекомендую вам создать копию исходного набора данных перед использованием кода.
Вы можете поместить этот код в обычный модуль в Excel, и в случае, если вам нужно часто его использовать и он нужен во всех ваших книгах Excel, вы можете сохранить этот код либо в личной книге макросов, либо добавить его как Надстройка Excel.
Если вы хотите использовать его одним щелчком мыши, вы можете добавить этот код макроса на панель быстрого доступа. Таким образом, все, что вам нужно сделать, это сделать выбор и щелкнуть кнопку макроса в QAT, чтобы запустить код.
Разрыв строки Vs. Возврат каретки
Между разрывом строки в Excel и возвратом каретки есть небольшая разница.
Функция возврата каретки — поместить курсор в начало предложения. Он использовался в ранних версиях пишущих машинок, а затем и в компьютерах. Сам по себе возврат каретки не приведет к разрыву строки в Excel. В сочетании с переводом строки приводит к разрыву строки.
В большинстве случаев, когда вы видите разрыв строки в Excel, в нем есть возврат каретки.
Кроме того, когда вы используете ALT Enter для вставки разрыва строки в Excel, он вставляет только перевод строки, но не возврат каретки.
Тогда зачем нам беспокоиться о возврате каретки? Потому что вы можете получить его в своих данных, если он был загружен из базы данных, которая использует возврат каретки.
Код возврата каретки в формате ASCII — 13 (CR, код ASCII 13), и вы можете найти и заменить его с помощью формул.
Вы можете узнать больше о разнице между переводом строки и возвратом каретки здесь.
Как убрать разрыв строки в экселе
Удаление разрывов строк из ячеек в Excel
В этом руководстве представлены три формулы, которые помогут вам удалить разрывы строк (которые возникают при нажатии клавиш Alt + Enter в ячейке) из определенных ячеек в Excel.
Удалите разрывы строк с помощью функции CLEAN
Общая формула
=CLEAN(text)
Аргумент
Текст: Текст, из которого вы хотите удалить разрывы строк. Это может быть:
- 1. Текстовая строка, заключенная в кавычки;
- 2. Или ссылку на ячейку, содержащую текстовую строку.
Как пользоваться этой формулой?
1. Выберите пустую ячейку для вывода результата.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
=CLEAN(B3)
Примечание: Если в ячейке есть другие непечатаемые символы, функция CLEAN также удалит их.
Удалите разрывы строк с помощью функции TRIM
Общая формула
=TRIM(text)
Аргумент
Текст: Текст, из которого вы хотите удалить разрывы строк. Это может быть:
- 1. Текстовая строка, заключенная в кавычки;
- 2. Или ссылку на ячейку, содержащую текстовую строку.
Как пользоваться этой формулой?
1. Выберите пустую ячейку для вывода результата.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
=TRIM(B3)
Примечание: Функция TRIM удаляет не только разрывы строк, но также все лишние пробелы и сохраняет отдельные пробелы между словами.
Удалите разрывы строк с помощью функции ЗАМЕНА
Общая формула
=SUBSTITUTE(cell,CHAR(10),»»)
Аргумент
Моб: В ячейке есть разрывы строк, которые нужно удалить.
Как пользоваться этой формулой?
1. Выберите пустую ячейку, чтобы вывести результат без разрывов строки.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения, чтобы применить формулу к другим ячейкам.
=SUBSTITUTE(C5,CHAR(10),»»)
Примечание: В этом случае все разрывы строк в определенных ячейках заменяются пустым текстом. Если вы хотите заменить разрывы строк другими конкретными символами, такими как запятая, заключите запятую в кавычки, как показано в приведенной ниже формуле:
=SUBSTITUTE(C5,CHAR(10),»,»)
Связанные функции
Функция ЧИСТКА в Excel
Функция Microsoft Excel CLEAN используется для удаления всех непечатаемых символов из данного текста.
Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.
Функция ПОДСТАВИТЬ в Excel
Функция Microsoft Excel SUBSTITUTE заменяет текст или символы в текстовой строке другим текстом или символами.
Функция СИМВОЛ в Excel
Функция Microsoft Excel CHAR возвращает символ, заданный числом от 1 до 255.
Родственные формулы
Удалить текст из ячейки, сопоставив содержимое
В этой статье показано, как использовать функцию ЗАМЕНА для удаления части текстовой строки из указанных ячеек путем сопоставления содержимого.
Удалить текст из ячейки на основе определенной позиции
В этом руководстве объясняется, как использовать формулы для удаления текста из ячейки на основе определенной позиции в Excel.
Удалить текст в зависимости от положения переменной в Excel
В этом руководстве объясняется, как удалить текст или символы из ячейки, когда она находится в переменной позиции.
Удалить ненужные символы из ячейки в Excel
Вы можете использовать функцию ЗАМЕНА, чтобы удалить любые нежелательные символы из определенной ячейки в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel — поможет вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80% времени.