Часто требуется внутри одной ячейки Excel сделать перенос текста на новую строку. То есть переместить текст по строкам внутри одной ячейки как указано на картинке. Если после ввода первой части текста просто нажать на клавишу ENTER, то курсор будет перенесен на следующую строку, но другую ячейку, а нам требуется перенос в этой же ячейке.
Это очень частая задача и решается она очень просто - для переноса текста на новую строку внутри одной ячейки Excel необходимо нажать ALT+ENTER (зажимаете клавишу ALT, затем не отпуская ее, нажимаете клавишу ENTER)
Как перенести текст на новую строку в Excel с помощью формулы
Иногда требуется сделать перенос строки не разово, а с помощью функций в Excel. Вот как в этом примере на рисунке. Мы вводим имя, фамилию и отчество и оно автоматически собирается в ячейке A6
В открывшемся окне во вкладке "Выравнивание" необходимо поставить галочку напротив "Переносить по словам" как указано на картинке, иначе перенос строк в Excel не будет корректно отображаться с помощью формул.
Как в Excel заменить знак переноса на другой символ и обратно с помощью формулы
Можно поменять символ перенос на любой другой знак , например на пробел, с помощью текстовой функции ПОДСТАВИТЬ в Excel
Рассмотрим на примере, что на картинке выше. Итак, в ячейке B1 прописываем функцию ПОДСТАВИТЬ:
Код:
ПОДСТАВИТЬ(A1;СИМВОЛ(10);" ")
A1 - это наш текст с переносом строки;
СИМВОЛ(10) - это перенос строки (мы рассматривали это чуть выше в данной статье);
" " - это пробел, так как мы меняем перенос строки на пробел
Если нужно проделать обратную операцию - поменять пробел на знак (символ) переноса, то функция будет выглядеть соответственно:
Код:
ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))
Напоминаю, чтобы перенос строк правильно отражался, необходимо в свойствах ячеек, в разделе "Выравнивание" указать "Переносить по строкам".
Как поменять знак переноса на пробел и обратно в Excel с помощью ПОИСК - ЗАМЕНА
Бывают случаи, когда формулы использовать неудобно и требуется сделать замену быстро. Для этого воспользуемся Поиском и Заменой. Выделяем наш текст и нажимаем CTRL+H, появится следующее окно.
Если нам необходимо поменять перенос строки на пробел, то в строке "Найти" необходимо ввести перенос строки, для этого встаньте в поле "Найти", затем нажмите на клавишу ALT, не отпуская ее наберите на клавиатуре 010 - это код переноса строки, он не будет виден в данном поле .
После этого в поле "Заменить на" введите пробел или любой другой символ на который вам необходимо поменять и нажмите "Заменить" или "Заменить все".
Кстати, в Word это реализовано более наглядно.
Если вам необходимо поменять символ переноса строки на пробел, то в поле "Найти" вам необходимо указать специальный код "Разрыва строки", который обозначается как ^l
В поле "Заменить на:" необходимо сделать просто пробел и нажать на "Заменить" или "Заменить все".
Вы можете менять не только перенос строки, но и другие специальные символы, чтобы получить их соответствующий код, необходимо нажать на кнопку "Больше >>", "Специальные" и выбрать необходимый вам код. Напоминаю, что данная функция есть только в Word, в Excel эти символы не будут работать.
В Эксель существует сразу несколько вариантов перенести текст внутри одной ячейки.
1 способ
Нужно воспользоваться инструментом форматирования ячейки.
1) Щёлкните правой кнопкой мыши на нужной ячейке или сразу на нескольких ячейках, в которых требуется переносить текст. В контекстном меню выберите пункт «Формат ячеек».
2) Открывается окно форматирования. Нужно открыть вкладку «Выравнивание» и в блоке «Отображение» поставить галочку на пункте «Переносить по словам».
3) Осталось нажать на кнопку «OK». Текст будет перенесён и будет отображаться не в одной строке, а в нескольких.
2 способ
1) Выделите нужную ячейку, для этого достаточно щёлкнуть на ней левой кнопкой мыши.
2) На панели инструментов программы Excel нажмите на кнопку «Перенос текста».
Этот способ гораздо удобнее, чем предыдущий, и тратится меньше времени.
3 способ
1) Перейдите в режим редактирования текста, для этого щёлкните 2 раза левой кнопкой мыши внутри ячейки. Курсор при этом нужно установить перед той частью текста, которую требуется перенести.
2) Теперь наберите на клавиатуре комбинацию клавиш "Alt" + "Enter". Текст будет разделён.
3) Чтобы увидеть окончательный результат достаточно выйти из режима редактирования ячейки.
Для выхода из режима редактирования можно нажать клавишу "Enter" или щёлкнуть 1 раз левой кнопкой мыши на любой другой ячейке таблицы.
Довольно часто возникает вопрос, как сделать перенос на другую строку внутри ячейки в Экселе? Этот вопрос возникает когда текст в ячейке слишком длинный, либо перенос необходим для структуризации данных. В таком случае бывает не удобно работать с таблицами. Обычно перенос текста осуществляется с помощью клавиши Enter. Например, в программе Microsoft Office Word. Но в Microsoft Office Excel при нажатии на Enter мы попадаем на соседнюю нижнюю ячейку.
Итак нам требуется осуществить перенос текста на другую строку. Для переноса нужно нажать сочетание клавиш Alt+ Enter . После чего слово, находящееся с правой стороны от курсора перенесется на следующую строку.
Автоматический перенос текста в Excel
В Экселе на вкладке Главная в группе Выравнивание есть кнопка «Перенос текста». Если выделить ячейку и нажать эту кнопку, то текст в ячейке будет переноситься на новую строку автоматически в зависимости от ширины ячейки. Для автоматического переноса требуется выполнить простое нажатие кнопки.
Убрать перенос с помощью функции и символа переноса
Для того, что бы убрать перенос мы можем использовать функцию ПОДСТАВИТЬ.
Функция заменяет один текст на другой в указанной ячейке. В нашем случае мы будем заменять символ пробел на символ переноса.
Синтаксис формулы:
ПОДСТАВИТЬ (текст;стар_текст;нов_текст;[номер вхождения])
Итоговый вид формулы:
ПОДСТАВИТЬ(A1;СИМВОЛ(10); » «)
- А1 – ячейка содержащая текст с переносами,
- СИМВОЛ(10) – символ переноса строки,
- » » – пробел.
Если же нам наоборот требуется вставить символ переноса на другую строку, вместо пробела проделаем данную операцию наоборот.
ПОДСТАВИТЬ(A1; » «;СИМВОЛ(10))
Что бы функция работало корректно во вкладке Выравнивание (Формат ячеек) должен быть установлен флажок «Переносить по словам».
Перенос с использование формулы СЦЕПИТЬ
Для решения нашей проблемы можно использовать формулу СЦЕПИТЬ.
Синтаксис формулы:
СЦЕПИТЬ (текст1,[текст2]…)
У нас имеется текст в ячейках A1 и B1. Введем в B3 следующую формулу:
СЦЕПИТЬ(A1;СИМВОЛ(10);B1)
Как в примере, который я приводил выше, для корректной работы функции в свойствах требуется установить флажок «переносить по словам».
В одни и те же слова разные пользователи вкладывают разный смысл. Так, под словом строка, одни понимают строковую переменную, другие совокупность горизонтально ориентированных ячеек, а третьи - строку текста в пределах ячейки. В этой публикации рассмотрим перенос строк, имея в виду, что смысл слов может быть разным.
Как перенести строки в Excel на новый лист?
Как известно, переносить строки с одного листа на другой можно либо одним диапазоном (когда каждая следующая строка имеет с предыдущей общую границу), либо только по одной. Все попытки выделить несколько несвязных строк, а затем одним действием вырезать выделенные строки, неизбежно заканчиваются появлением сообщения "Данная команда неприменима для несвязных диапазонов. Выберите один диапазон и снова выберите конманду." Таким образом, приложение Excel ставит пользователя перед выбором, либо вырезать и вставлять строки по одной, что выглядит мало привлекательным занятием, либо искать другие способы решения задачи. Одним из таких способов является использование надстройки для Excel, которая позволяет достаточно быстро перенести строки с заданными значениями и удовлетворяющие заданным условиям на новый лист.
надстройка для переноса строк по условию
Пользователю дается возможность:
1. Быстро вызывать диалоговое окно надстройки одним кликом по кнопке, расположенной на одной из вкладок ленты Excel;
2. Задавать как одно, так и несколько значений для поиска, используя в качестве знака-разделителя ";" точку с запятой;
3. Учитывать либо не учитывать регистр при поиске текстовых значений;
4. Использовать в качестве диапазона ячеек для поиска заданных значений как используемый диапазон, так и любой произвольный диапазон листа, заданный пользователем;
5. Выбирать одно из восьми условий для ячеек выбранного диапазона:
а) совпадает либо не совпадает с искомым значением;
б) содержит либо не содержит искомое значение;
в) начинается либо не начинается с искомого значения;
г) заканчивается либо не заканчивается искомым значением.
6. Устанавливать ограничения для выбранного диапазона, используя номера строк.
Перенос строки в ячейке
Как перенести текст в ячейке на новую строку
Для того чтобы в пределах одной ячейки перенести текст на новую строку, необходимо для переноса текста нажимать сочетание клавиш Alt+Enter.
Как заменить перенос строки на пробел
Часто требуется избавиться от переносов строк, удалить их, чтобы привести значения ячеек к обычному виду. Если посмотреть в таблицу ASCII-кодов,
можно увидеть, что приложением используется код с номером 10 и названием "Перевод строки", которому соответствует сочетание клавиш Ctrl+J. Зная это, можно удалить переносы строки при помощи стандартного средства Excel, таким как "Найти и заменить". Для этого необходимо вызвать диалоговое окно сочетанием клавиш Ctrl+F (при этом поле останется пустым), перейти на вкладку "Заменить", поместить курсор в поле "Найти", нажать сочетание клавиш Ctrl+J и нажать кнопку "Заменить" либо "Заменить все". Аналогичным образом можно заменить перенос строки на пробел.
Большинство настроек Microsoft Excel задаются по умолчанию, среди них ограничение: одна ячейка – одна строка. Но бывает множество случаев, когда в ячейке нужно сделать несколько строчек. Для этого можно использовать несколько способов.
Помимо расположения слов в одной ячейке, часто оказывается проблемой написание длинного слова: либо его часть переносится в другую строку (при использовании Enter), либо оно заезжает на соседний столбец.
Офисный продукт Excel на многие задачи имеет вариативную линейку решений. Перенос строки в ячейке Excel не исключение: от разрыва вручную до автоматического переноса с заданной формулой и программирования.
Используем комбинацию клавиш
В этом случае используется сочетание клавиш «Alt+Enter ». Пользователю будет удобнее сначала зажать «Alt», затем, не отпуская клавиши нажать «Enter ».
Переносить текст в ячейке этой комбинацией можно в момент набора или после того, как все слова написаны в одну строчку. Нужно перед нужным словом поставить курсор и нажать указанное сочетание клавиш – это сделает новую строку в ячейке.
Когда требуется написать большой текст можно применить команду «формат ячеек », что позволит уместить текст в одной ячейке Эксель.
Нажатием мышки выделить
требуемую область. Правой кнопкой мыши вызвать диалоговое окно с командами. Выбрать «формат ячеек
».
На верхней панели выделить раздел «выравнивание
».
Поставить галочку против команды «переносить по словам
» (графа «отображение»). Нажать кнопку ОК.
После чего диалоговое окно закроется. Теперь ячейка заполняется длинным текстом, перенос слов будет осуществляться автоматически
. Если слово не помещается по ширине, то программа Excel сама перенесет строку в ячейке.
Если таких ячеек должно быть несколько, нужно выделить их все левой кнопкой мышки и произвести указанный выше алгоритм действий. Во всей выделенной области появится автоматический перенос.
Использование формул
Когда пользователю нужно не только поделить текст на строки, но предварительно собрать информацию с нескольких мест, то вышеперечисленные способы не подходят. В этом случае применяют несколько видов формул переноса по словам:
- символ ();
- сцепить ();
- подставить ().
Символ
Внутри скобок указывается код – цифровое значение от 1 до 255. Берется код из специальной таблицы, где указано число и соответствующий ему символ переноса. Перенос имеет код – 10. Следовательно, используется формула – «символ(10)».
Разберем на конкретном примере, как работать с формулой «символ(10)». Для начала заполним ячейки, которые впоследствии объединим. 4 столбца первой строки – фамилия, пол, возраст, образование. Вторая – Иванова, жен, 30, высшее.
Выбираем, затем выделяем ячейку
, куда осуществим перенос. Ставим курсор в строку формул.
Заполняем формулу (для выбранной ячейки):
A1&A2&СИМВОЛ(10)&B1&B2&СИМВОЛ(10)&C1&C2&СИМВОЛ(10)&D1&D2
Где знак «&» означает сцепление соответствующих ячеек, а символ(10) – перенос строки после каждой сцепленной пары.
После написания формулы нажать клавишу «Enter
». В выделенной области появится результат.
На скриншоте видно, что не задан формат. Используем «формат строки
», как было указано выше. После того, как будет отмечена галочка напротив пункта «переносить по словам
», ячейка приобретет вид, как на картинке ниже.
Есть еще один способ быстро использовать данную команду. Справа вверху есть раздел «Формат
». Следует нажать на маленькую черную стрелочку, чтобы вызвать диалоговое окно. Внизу находиться искомая команда.
Если заменить исходные данные другими, содержимое ячейки также будет меняться.
Сцепить
Функция «сцепить()» похожа на предыдущую. Она тоже задается формулой, но в скобках указывается не код, а формула «символ(10)».
Возьмем для примера только первую строку с 4 столбцами.
Выделим область для переноса и направим курсор на строку формул. Запишем:
СЦЕПИТЬ(A1;СИМВОЛ(10);B1;СИМВОЛ(10);C1;СИМВОЛ(10);D1)
Нажмем клавишу «Enter
».
Зададим «переносить по словам
». Получим:
Количество ячеек
для сцепления может быть любое.
Главное достоинство этого способа — смена данных строк и столбцов не меняет формулу, она будет иметь тот же заданный алгоритм.
Подставить
Когда ячейка вмещает много слов и нужно их переместить в другое место сразу с переносом, то пользуются формулой «подставить()».
Вносим нужный текст в А4.
Затем левой мышкой выделим А6, запишем в сроке формул:
ПОДСТАВИТЬ(A4;" ";СИМВОЛ(10))
В формулу вставляем адрес ячейки с текстом – А4. После нажатия клавиши «Enter
» получим результат.
Важно не забывать для выделенной области ставить галочку напротив команды «переносить по словам ».
Замена знака переноса на пробел и обратно
Иногда нужно заменить знак переноса на пробел, сделать сплошным текстом столбик слов. Есть несколько способов это осуществить. Рассмотрим два из них:
- окно «найти и заменить »;
- VBA скрипты .
Поиск и замена открывается сочетанием клавиш Ctrl+H
. Для удобства сначала нужно зажать клавишу Ctrl, затем нажать английскую буква H. Всплывет диалоговое окно с настраиваемыми параметрами.
В поле «найти
» следует ввести Ctrl+J
(сначала зажав клавишу, после набрав букву). При этом поле останется практически пустым (будет лишь едва заметная мигающая точка).
В поле «заменить на » нужно поставить пробел (или несколько), а также любой другой знак, на который планируется менять перенос.
Программа выделит область файла с искомыми значениями. После чего останется нажать «заменить все
».
Столбики из слов перестроятся в строчки с пробелами.
Используем скрипт VBA
Открыть окно редактора можно сочетанием клавиш Alt+F11
.
В редакторе находим панель «VBAProject
» и кликаем на искомый файл. Правой кнопкой вызываем контекстное меню. Выбираем сначала «Insert
», затем «Module
».
Появляется окно для вставки кода.
Впечатываем туда код. Если нужно заменить пробел на перенос , то пишем:
Sub ЗаменяемПробелы() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(32), Chr(10)) Next End Sub
Если наоборот :
Sub ЗаменяемПереносы() For Each cell In Selection cell.Value = Replace (cell.Value, Chr(10), Chr(32)) Next End Sub
Где Chr (10) – это код переноса строки, а Chr (32) – код пробела.
Всплывет окно, где нужно нажать кнопку «НЕТ
».
Далее необходимо сохранить документ
с поддержкой нового макроса.
Чтобы закрыть редактор, нажимаем «Alt+Q ».
Главный минус этого способа – требуется базовое знание языка VBA.
Вариативные решения задачи позволяет пользователю Microsoft Excel выбрать подходящий для себя способ.