Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Если вы иногда создаете документы в Microsoft Excel, то, вероятно, заметили, что все данные, введенные в ячейку, записываются на одной строке. Поскольку это не всегда подходит, и поскольку возможность растянуть ячейку тоже не подходит, необходимо переместить текст. Не следует нажимать «Enter» как обычно, потому что курсор сразу же перескакивает на новую строку, и что с того?

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

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

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

Давайте рассмотрим этот вопрос подробнее.

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

Если нам нужно избавиться от дефисов, первое, что обычно приходит на ум, это классическая техника «поиск и замена». Мы выделяем текст, а затем вызываем окно замены с помощью Ctrl + H или Главная — Найти и выделить — Заменить. Одна аномалия — не очень понятно, как ввести в верхнем поле Find, что такое наш невидимый символ переноса строки. Alt + Enter здесь, к сожалению, не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получится.

Читайте так же:
Как проверить, пусты ли текстовые поля в пользовательской форме или нет в Excel?

Поможет Ctrl + J — это альтернатива Alt + Enter в диалоговых окнах или полях ввода Excel:

Замена переносов строк на пробел

Обратите внимание, что после того, как вы поместите мигающий курсор в верхнее поле и нажмете Ctrl + J — в самом поле ничего не появится. Пожалуйста, не пугайтесь — символ невидим 🙂

В поле Replace with ниже введите ничего или пробел (если вы не хотите просто удалить скрипт, но замените его пробелом, чтобы строки не слипались). Теперь нам остается только нажать на кнопку Replace All, и наш скрипт исчезнет:

Исправленный текст

N.B. Когда вы заменяете символ с помощью Ctrl + J, невидимый символ появляется в поле Найти и может мешать в дальнейшем — не забудьте удалить его, поместив курсор в это поле и нажав клавиши Delete и Backspace несколько раз (для надежности).

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

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

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

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

Замена переносов строк формулой

Если вы хотите заменить Alt + Enter, например, на пробел, то потребуется несколько более сложная конструкция:

Замена переноса строки на пробел формулой

Чтобы определить невидимый дефис, мы используем функцию CHAR, которая выдает символ по его коду (10). Затем функция SUBSTITUTE ищет наш дефис в исходных данных и заменяет его любым другим текстом, например, пробелом.

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

Используя знакомый и очень удобный инструмент Text to Columns из вкладки Data (Data — Text to Columns), вы также можете разделить текст по необходимости с помощью Alt + Enter, разбив его на несколько колонок. Это можно сделать, выбрав на втором шаге мастера пользовательский символ-разделитель (Custom) и используя привычную комбинацию клавиш Ctrl + J вместо Alt + Enter:

Читайте так же:
Как преобразовать текст в функцию столбца в Excel?

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

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

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

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

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

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

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

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

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

Чтобы загрузить необработанные данные в Power Query, необходимо сначала преобразовать их в таблицу Smart Table, нажав Ctrl + T или кнопку Format as Table на вкладке Home. Если по каким-то причинам вы не хотите или не можете использовать «умные» таблицы, вы также можете работать с «глупыми» таблицами. В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы — Менеджер имен — Новый.

Затем можно перейти на вкладку «Данные» (если у вас Excel 2016 или более поздняя версия) или вкладку «Power Query» (если у вас Excel 2010-2013) для загрузки нашей таблицы в редактор Power Query:

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

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

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

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

Читайте так же:
Как разделить лист на несколько панелей в Excel?

Убедитесь, что вы включили селектор By rows в группе дополнительных опций, чтобы разделить все на строки, а не столбцы.

Все, что вам нужно сделать, это нажать OK и получить то, что вы хотите:

Результаты

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

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

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

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

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

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

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

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

Читайте так же:
Как преобразовать логическое значение true / false в число (1/0) или текст в Excel?

стрелка синий правый пузырьПринудительный перенос строки в ячейке

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

Поместите курсор в то место внутри ячейки, где вы хотите принудительно разрывать строку, и нажмите Alt + Enter ключи вместе. Теперь сразу вставляется разрыв строки.
разрыв строки документа 2

После этого можно вставлять каждый перенос строки по очереди.

стрелка синий правый пузырьАвтоматический разрыв строки внутри ячейки

Если вы хотите автоматически вставить переносы строк в ячейку, вы можете применить утилиту «Перенос текста».

Выберите ячейки, в которые вы хотите автоматически добавлять разрывы строк, и нажмите Главная > Перенести текст для Перенести текст функция. Смотрите скриншот:
разрыв строки документа 3

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector