Перенос на другую строку в ячейке. Тонкости работы с переносами строк в Excel

Тонкости работы с переносами строк в Excel

Переносы строк снутри одной ячейки, добавляемые с помощью сочетания кнопок Alt+Enter – дело очень нередкое и обычное. Время от времени их делают сами юзеры, чтоб добавить красы длинноватому тексту. Время от времени такие переносы добавляются автоматом при выгрузке данных из каких-то рабочих программ (привет 1С, SAP и т.д.) Неувязка в том, что на такие таблицы приходится позже не просто наслаждаться, а с ними работать – и вот тогда эти невидимые знаки переноса могут стать неувязкой. А могут и не стать – ежели уметь верно с ними обращаться.

Давайте-ка мы разберёмся в этом вопросце поподробнее.

Удаление переносов строк заменой

Ежели нам необходимо избавиться от переносов, то 1-ое, что традиционно приходит в голову – это классическая техника “найти и заменить”. Выделяем текст и потом вызываем окно подмены сочетанием кнопок 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 и скопируйте туда нижеприведённый код:

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, чтоб запустить сделанный макрос, который и сделает за вас всю работу:

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

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

Как сделать перенос строчки в ячейке в Excel?

Рассмотрим разные методы как можно сделать перенос текста снутри ячеек в Excel — в виде ручного ввода знака переноса либо средством внедрения формул.


Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

При работе с текстовыми данными в Excel мы нередко сталкиваемся с неувязкой разделения текста по различным строчкам, время от времени нам необходимо перенести текст, чтоб запись стала наиболее малогабаритной, читаемой, полностью помещалась в ячейке и т.д.

В принципе в Excel для этого есть обычная команда Перенести текст, или в панели вкладок выбираем Главная -> Выравнивание:

Читайте также  Почему мышка на компе не работает. Что делать, если не работает мышка

Либо щелкаем правой клавишей мышки по ячейке, во всплывающем окне избираем Формат ячеек и на вкладке Выравнивание ставим галочку напротив Переносить текст:

Применение данной нам команды дозволяет нам переносить слова в строке в зависимости от ширины столбца (по принципу шире столбец — меньше строк, уже столбец — больше строк).

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

Давайте разберемся что же делать в таком случае?

Ручной ввод переноса текста в строке

Самый обычный вариант — сделать перенос текста в ячейке вручную.

Для ручного переноса строчки воспользуемся сочетанием кнопок Alt + Enter — заходим в ячейку, ставим курсор в необходимое место, жмем указанное сочетание кнопок и текст (справа от курсора) переносится на последующую строку:

Тут все чрезвычайно просто, никаких сложностей появиться не должно.

Однако ежели же стоит задачка как-то заавтоматизировать процесс либо вначале сам текст задается формулой, то таковой вариант уже не подходит.

Поэтому перейдем к рассмотрению формульного аналога.

Формульный ввод переноса текста в ячейке

Предположим, что у нас имеются 2 ячейки с данными и мы желаем в еще одну ячейку записать содержимое первых 2-ух с переносом текста на новейшую строку.

Например, чрезвычайно нередко в таблицах указываются периоды и для сокращения вида записи даты склеим месяц и год, чтоб они разносились по различным строчкам в одной ячейке:

В ячейку A3 введем формулу A1&A2, где A1 — месяц, а A2 — год:

Так как никаких доп действий с формулой мы не делали, то мы лицезреем, что тут текст на другую строчку не переносится.

В целом для переноса строчки мы можем пользоваться 2 основными способами:

  • Добавить разрыв строчки как в примере выше (с помощью композиции клавиш Alt + Enter);
  • Записать знак переноса с помощью формулы.

В первом случае перепишем формулу и добавим в нее текстовую строчку, которая будет состоять лишь из знака переноса строки:

Во втором же случае мы запишем знак переноса в явном виде — с помощью формулы.

Для этих целей воспользуемся обычной функцией СИМВОЛ (в британской версии CHAR), которая по числовому коду дозволяет вернуть хоть какой знак системы ASCII.

В системе ASCII знаку разрыва строчки соответствует код 10, потому еще раз перепишем формулу с применением новейшей функции:

Все готово.

Важное замечание

Обратите внимание, для того, чтоб текст корректно переносился по различным строчкам нужно включить свойство Перенести текст.

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

Спасибо за внимание!
Если у вас есть мысли либо вопросцы по теме статьи — пишите и спрашивайте в комментариях.

Удачи для вас и до скорых встреч на страничках блога TutorExcel.Ru!

Оставьте комментарий

Adblock
detector