Как перенести строку в ячейку Excel

Excel

Что такое перенос текста в ячейке?

Когда ввод слишком велик, происходит одно из двух:

  • Если столбцы справа пусты, длинная фраза вытекает из ячейки в эти столбцы.
  • Если в окрестности справа есть какие-либо данные, они обрезаются до границы ячейки.

На рисунке ниже показаны эти два случая:

перенос слов в ячейку может помочь вам завершить длинное предложение, не «заползая» в другие ячейки. Этот термин означает отображение содержимого ячейки на нескольких строках вместо одной длинной строки. Это позволит избежать эффекта «усеченного столбца», сделает таблицу более удобной для чтения и печати. Кроме того, это позволяет сохранять одинаковую ширину столбца по всему листу.

На скриншоте показано, как это выглядит:

А теперь разберем несколько способов — как сделать перенос и добиться такого эффекта.

Способы переноса текста

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

Способ 1: использование клавиатуры

Самый простой способ переноса на другую строку — поставить курсор перед сегментом, который вы хотите перенести, а затем ввести на клавиатуре комбинацию клавиш Alt (влево) + Enter.

Ячейка, в которую вы хотите переносить слова в Microsoft Excel

В отличие от использования только одной кнопки Enter, этот метод позволит достичь точно указанного результата.

Перенос слов реализован в Microsoft Excel

Способ 2: форматирование

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

  1. Выберите ячейку, в которой текст выходит за границы. Щелкаем по нему правой кнопкой мыши. В открывшемся списке выберите пункт «Форматировать ячейки…».Переключиться на формат ячейки в Microsoft Excel
  2. Откроется окно форматирования. Перейдите на вкладку «Выравнивание». В блоке настроек «Отображение» выберите опцию «Переносить по словам», поставив галочку. Нажмите на кнопку «ОК».

Формат ячейки в Microsoft Excel

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

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

Читайте также: Корреляционный анализ в Excel: 2 рабочих варианта

Способ 3: использование формулы

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

  1. Отформатируйте ячейку, как описано в предыдущем варианте.
  2. Выберите ячейку и введите в нее или в строку формул следующее выражение:=СЦЕПИТЬ(«ТЕКСТ1″,СИМВОЛ(10);»ТЕКСТ2»)

    Вместо элементов «ТЕКСТ1» и «ТЕКСТ2» нужно заменить слова или наборы слов, которые вы хотите передать. Остальные символы формулы изменять не нужно.

    Использование функции СЦЕПИТЬ в Microsoft Excel

  3. Чтобы результат появился на листе, нажмите кнопку Enter на клавиатуре.

Word упакован с функцией в Microsoft Excel

Основным недостатком этого метода является то, что он сложнее в исполнении, чем предыдущие варианты.

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

Деление на столбцы по переносу строки

Известный и очень удобный инструмент Текст по столбцам из вкладки Данные (Данные — Текст в столбцы) также отлично умеет работать с разрывами строк и разбивать текст из одной ячейки на несколько, разбивая его по Alt+Enter. Для этого на втором шаге мастера нужно выбрать вариант пользовательского разделителя Other (Пользовательский) и использовать уже известное нам сочетание клавиш Ctrl+J как альтернативу Alt+Enter:

Разделение столбцов с помощью Alt+Enter

Если ваши данные могут содержать несколько разрывов строк подряд, вы можете «скрыть» их, установив флажок «Обрабатывать последовательные разделители как один).

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

Результат дивизиона

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

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

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

Разделить на строки с помощью Alt+Enter

Вручную это делать долго, формулами — сложно, макросом — не каждый напишет. Но на практике эта проблема встречается чаще, чем хотелось бы. Самым простым и легким решением будет использование для этой задачи надстройки Power Query, которая встроена в Excel с 2016 года, а для предыдущих версий 2010-2013 годов ее можно скачать совершенно бесплатно с сайта Microsoft.

Чтобы загрузить исходные данные в Power Query, необходимо сначала преобразовать их в «умную таблицу» с помощью горячей клавиши Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Главная — Форматировать как таблицу). Если по каким-то причинам вы не хотите или не можете использовать «умные» таблицы, то вы можете работать с «тупыми». В этом случае достаточно выбрать исходную область и дать ей имя на вкладке Формулы — Диспетчер имен — Создать (Formulas — Name Manager — New).

После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) вы можете нажать кнопку Из таблицы/диапазона, чтобы загрузить нашу таблицу в Power — редактор вопрос:

Загружаем нашу таблицу в Power Query

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

Разделить столбец на строки в Power Query

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

Чтобы все было разделено на строки, а не столбцы, не забудьте переключить переключатель «По строкам» в группе «Дополнительные параметры.

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

Полученные результаты

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

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

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

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

Sub Split_By_Rows() Затемнить ячейку как диапазон, n как целое число Установить ячейку = 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 in End Sub

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

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

Автоматический перенос текста

Выберите ячейки на листе, которые вы хотите отформатировать.
На вкладке Главная ► Обтекание текстом (выравнивание):

Автоматический перенос текста
Автоматический перенос текста Автоматический перенос текста

Данные в ячейке будут переноситься в соответствии с шириной столбца при ее изменении.
Если текст отображается не полностью, укажите точную высоту строки (столбца), либо текст находится в объединенных ячейках.

Ручной ввод разрыва строки

Вы можете начать новую строку текста в любом месте ячейки.

  1.  Дважды щелкните ячейку или выберите ячейку, а затем нажмите F2 в том месте, где вы хотите вставить разрыв строки.
  2.  Дважды щелкните ячейку, в которую вы хотите вставить разрыв строки, и нажмите Alt+Enter:

Ручной перенос с помощью Alt+EnterРучной перенос с помощью Alt+Enter Результат ручного переноса строки Результат ручного переноса строки

Автоматический перенос строки не всегда подходит. Если нам нужны четкие отступы, используйте ручной перенос строк.

Разница между автоматическим и ручным переносом строк Разница между автоматическим и ручным переносом строк

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

Хорошо, мы разобрались с переносами строк, но как насчет ситуации, когда переносы строк не нужны?

Для автоматической расстановки переносов их нужно только отключить на вкладке Главная ► Разрыв текста (настройка).

Для специальных символов это сложнее, вам нужно искать и заменять непечатаемый символ возврата каретки.

  1.  Выберите область, в которой вы хотите удалить дефисы.
  2. Нажмите комбинацию клавиш Ctrl+H, в открывшемся окне выберите курсором поле Найти и нажмите комбинацию клавиш Ctrl+J.

Вроде бы ничего не произошло, но для Excel запрос ясен:

Щелкните Заменить. Прозрачный!

Если в строке перед переносом нет пробела, заменитель его не добавит!

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

Решить проблему можно с помощью встроенной функции CLEAN, которая очищает текст от всех непечатаемых символов, включая разрывы строк:

Если в строке перед переносом нет пробела, формула его не добавит!

Более продвинутый вариант с макросами:

Sub RemoveCarriageReturnsSelection() ‘Удалить возврат каретки в выбранном диапазоне

Selection.Replace What:=Chr(10), Замена

Selection.Replace What:=Chr(160), Replace:=»» ‘ удалить символ «как» пробел

Selection.Replace What:=» «, Replace:=» » ‘ удалить двойной пробел

Конец сабвуфера

Sub RemoveCarriageReturnsSheet() ‘Удалить возврат каретки на листе

Cells.Replace What:=Chr(10), Замена

Cells.Replace What:=Chr(160), Replace:=»» ‘удалить символы «как» пробелы

Cells.Replace What:=» «, Replace:=» » ‘ удалить двойной пробел

Конец сабвуфера

Почему перенос строки в Excel не работает?

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

И все же, если это не сработает, ознакомьтесь со следующими советами по устранению неполадок.

1. Фиксированная высота строки

Если в ячейке таблицы видно не все содержимое, скорее всего, для нее принудительно задана определенная высота. Чтобы это исправить, выберите проблемное положение, перейдите на вкладку «Главная» > «Формат» > «Автоподбор высоты строки».
Или вы можете ввести конкретную высоту, щелкнув «Высота строки», а затем введя желаемое число в соответствующее поле. Фиксированная высота особенно полезна для управления отображением заголовков таблиц.

2. Объединенные ячейки

обтекание текстом в ячейке не работает, если несколько из них объединены в одну. Поэтому нужно определиться, какая функция наиболее важна в вашем случае. Если вы сохраните объединенные ячейки, вы можете отобразить все содержимое, сделав столбцы шире. Если вы выберете «Обтекание текста», вы можете разъединить их, нажав кнопку «Объединить и центрировать» на вкладке «Главная».

3. Достаточно места, чтобы обойтись без переноса.

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

Как удалить переносы строк из ячеек в Excel 2013, 2010 и 2007

Существует 3 способа удаления возврата каретки из полей. Некоторые из них реализуют замену новой строки. Обсуждаемые ниже параметры работают одинаково в большинстве версий редактора электронных таблиц.

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

Перенос строк в текстовой информации происходит по ряду причин. Общие причины включают такие вещи, как использование сочетания клавиш Alt + Enter, а также перенос текстовых данных с веб-страницы в рабочую область электронной таблицы. Нам нужно убрать возврат каретки, так как без этой процедуры невозможно реализовать нормальный поиск точных фраз.

Важно! Первоначально фразы «Перевод строки» и «Возврат каретки» использовались при работе на печатных станках и обозначали 2 разных действия. Персональные компьютеры были созданы с учетом функций печатных машин.

Удаляем возвраты каретки вручную

Разберем подробно первый способ.

  • Преимущество: быстрое выполнение.
  • Минусы: Отсутствие дополнительных функций.

Подробная инструкция выглядит так:

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

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

  1. С помощью клавиатуры нажмите комбинацию клавиш «Ctrl+H». На экране появилось окно под названием «Найти и заменить».
  2. Устанавливаем указатель на строку «Найти». С помощью клавиатуры нажмите комбинацию клавиш «Ctrl + J». В строке есть маленькая точка.
  3. В строке «Заменить на» вводим значение, которое нужно вставить вместо возврата каретки. Чаще всего используется пробел, так как он позволяет исключить склейку 2-х рядом стоящих предложений. Для реализации удаления переноса строк строка «Заменить на» не должна быть заполнена информацией.

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

  1. Используя ЛКМ, нажмите «Заменить все». Прозрачный! Мы реализовали возвратное удаление.

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

Удаляем переносы строк при помощи формул Excel

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

Подробная инструкция выглядит так:

  1. Давайте реализуем добавление дополнительного столбца в конце исходной информации. В этом примере он будет называться «1 строка»
  2. В 1 поле в дополнительном столбце (С2) вводим формулу, реализующую удаление или замену переносов строк. Для выполнения этой операции используется несколько формул. Формула, подходящая для использования с комбинациями возврата каретки и перевода строки: =SUBSTITUTE(REPLACE(B2,CHAR(13),»»);CHAR(10);»»).
  3. Формула, подходящая для замены разрыва строки каким-либо другим символом, выглядит так: В этом случае строки не будут объединены.
  4. Формула для удаления всех непечатаемых символов из текстовых данных: =ПЕЧАТЬ(B2).

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

  1. Копируем формулу и затем вставляем ее в каждую ячейку дополнительного столбца.
  2. Кроме того, вы можете заменить исходный столбец новым, где будут удалены разрывы строк.
  3. Делаем выделение всех ячеек, расположенных в столбце С. Зажимаем комбинацию «Ctrl+C» на клавиатуре, чтобы осуществить копирование информации.
  4. Выбираем поле B2. Нажмите комбинацию клавиш «Shift+F10». В появившемся небольшом списке нажмите ЛКМ на пункт с названием «Вставить».
  5. Реализуем удаление столбца справки.

Удаляем переносы строк при помощи макроса VBA

  • Преимущество: создание происходит только один раз. В дальнейшем этот макрос можно будет использовать в других табличных документах.
  • Недостаток: нужно понимать, как работает язык программирования VBA.

Для реализации этого способа необходимо войти в окно ввода макросов и ввести туда следующий код:

символ-переноса-строки-в-excel-как-сделать-перенос-строки-в-ячейке-excel-все-способы

Оцените статью
Блог о Microsoft Word