Как объединить перенос строк в excel

Приступая к работе в Microsoft Excel без должной подготовки, пользователь неизбежно столкнется с определенными трудностями, которые специалисту покажутся пустяковыми. Взять, к примеру, такую простую на первый взгляд операцию как перенос текста в ячейке. Пока вы набираете текст, Excel автоматически расширяет его по горизонтали, длинный текст отображается поверх соседних ячеек. А можно ли сделать так, чтобы длинный текст переносился на следующую строку и оставался в той же самой ячейке? Конечно можно, что мы сейчас и продемонстрируем.

Перенос текста в одной ячейке Excel

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

1. Выделите ячейку мышкой, нажмите по выделенной области правой кнопкой мыши и выберите «Формат ячеек»;
2. В открывшемся окне настроек на вкладке «Выравнивание» отметьте пункт «переносить по словам» и нажмите «OK»;
3. Сохранив настройки, можете смело набирать текст в созданной ячейке, не используя символ каретки;
4. По завершении ввода нажмите Enter. Текст будет сохранен в одной ячейке и останется виден целиком, а ячейка растянется по высоте.

А вот и второй, более простой способ сделать автоперенос текста в ячейке.

1. Выделите мышкой ячейку, затем на панели инструментов, в блоке «Выравнивание» нажмите пиктограмму «Перенести текст» (смотрите скриншот);
2. Всё готово, набирайте в ячейке ваш текст, а Excel сама позаботится о переносе его на новую строку в той же ячейке;
3. Нажмите Enter для сохранения результатов.

Перенос текста в объединенных ячейках Excel

А теперь рассмотрим способ вставки текста с переносом в объединенные ячейки. Для чего это нужно? Всё очень просто: вам может понадобиться крупная ячейка «на фоне» маленьких ячеек, используемых в Excel по умолчанию.

1. Выделите мышкой или с помощью комбинации Shift + Стрелки нужное количество ячеек;
2. Нажмите последовательно клавиши Alt → Я → Щ → Б (необязательно с включенным капсом), вследствие чего ячейки тут же будут объединены;
3. Нажмите на панели инструментов уже знакомую вам пиктограмму «Перенести текст»;
4. Вставьте в созданную ячейку ваш текст.

Тот же результат можно получить следующим образом:

1. Выделите требуемое количество ячеек, кликните по выделенной области и выберите в меню опцию «Формат ячеек»;
2. В открывшемся окне отметьте чекбоксы «переносить по словам» и «объединение ячеек»;
3. Нажмите «OK» и вставьте в ячейку ваш текст.

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

С помощью «горячих клавиш»

Самым простым способом переноса текста внутри ячейки Excel является использование комбинации Alt + Enter.

1. Установите курсор в ячейку и начинайте вводить текст;
2. В том месте, где вы хотите сделать перенос, нажмите Alt + Enter и продолжайте вводить текст с новой строки;
3. Завершив ввод текста, нажмите ввод. В результате вы увидите весь ваш текст в одной ячейке.

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

Перенос строки в ячейке Excel формулой

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

=СЦЕПИТЬ("Первая часть текста";СИМВОЛ(10);"вторая часть текста.")

Где СИМВОЛ(10) — это специальный невидимый символ переноса на новую строку, добавляемый между частями текста. К слову, когда вы нажимаете Alt + Enter, тот же символ переноса добавляется в конце строки. Заключенный в двойные кавычки текст может быть произвольным, это 2 фразы, которые должны быть расположены на разных строках внутри одной ячейки.

1. Выделите ячейку, вставьте в поле формул указанную формулу с подготовленным текстом и нажмите ввод;
2. После того объединенный текст будет вставлен, выделите первую ячейку с началом текста и нажмите на панели инструментов кнопку переноса строк, иначе спецсимвол не будет работать;
3. В результате вторая часть текста будет перенесена в ячейке на новую строку.

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

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

1. Выделите ячейку и вставьте в нее свой текст так, как есть. Он растянется по всей длине ячеек;
2. Нажмите ввод, выделите первую ячейку, в которой хотите оставить видимый текст, нажмите по ней правой кнопкой мыши и выберите в контекстном меню опцию «Формат ячеек»;
3. В открывшемся окне переключитесь на вкладку «Выравнивание», в выпадающем списке «По горизонтали» выберите значение «с заполнением».
4. Теперь длинный текст будет скрыт, его отображение ограничится шириной текущей ячейки.
5. Чтобы увидеть текст целиком, нажмите на ячейку с фрагментом текста. Одинарное нажатие отобразит его только в поле ввода формул, двойной клик — в поле для формул и по всей длине ячеек.

А вот и альтернативный способ.

1. Выделите ячейку и вставьте в нее свой текст так, как есть. Естественно, он растянется по всей длине ячеек;
2. Выделите соседнюю, заполненную частью текста ячейку и установите курсор в поле для вставки формул;

   

   

3. После того как выделенная ячейка станет пустой, нажмите пробел, а затем ввод. Лишний текст тут же будет скрыт, останется лишь его фрагмент в первой ячейке;

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

1. Объедините две или три ячейки с помощью инструмента «Объединить ячейки»;
2. Вставьте в удлиненную ячейку длинный текст и нажмите ввод. Лишний текст будет тут же скрыт;

   

   

3. Чтобы его увидеть, поступаем так же, как описано в пункте 5 предыдущего раздела.

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

Перенос строки в пределах ячейки в Microsoft Excel

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

​Как известно, по умолчанию​: Дело в том,​

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

​ макросов​RAN​Ячейку В3 раздвигаем​ строку?​​ имеет число 10.​​ функцию СИМВОЛ в​ диапазона образуется отдельная​ для нашего листа.​ одну большую ячейку,​ строки, и нажмите​ текста. Можно также​ в описании первого​ следующее выражение:​ Жмем на кнопку​ клавиш​

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

​ в одной ячейке​ что слово «работать»​0mega​: Сделать, чтобы текст​ вправо и расширяем​Serge_007​ В этой же​ нужное место формулы,​​ ячейка.​​Выделите ячейки, которые требуется​

​ объединив при этом​ сочетание клавиш ALT+ВВОД.​​ вручную ввести разрыв​​ способа. Третий вариант​=СЦЕПИТЬ(«ТЕКСТ1″;СИМВОЛ(10);»ТЕКСТ2»)​«OK»​Alt+Enter​

​ листа Excel располагается​​ просто не уместилось​

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

​: просто как 5​ переносился автоматически при​ вниз​: Здравствуйте.​ таблице содержатся непечатные​ которая генерирует значения​Объединить ячейки:​ объединить.​ несколько смежных.​К началу страницы​ строки для размещения​

1. ​ рекомендуется использовать только​Вместо элементов​.​.​ одна строка с​ по длине. Сделайте​ копеек …​​ достижении конца строки​​Топнули правой мышкой​

   

2. ​Использовать Word. Excel​ символы с клавиатуры.​​ в ячейках для​​Объединяет ячейки в​​Нажмите команду​​В следующем примере мы​​В данном уроке мы​​ текста на новую​ тогда, когда данные​​«ТЕКСТ1»​​После этого, если данные​

​В отличие от использования​ числами, текстом или​ пошире столбец AI​я знаю только​ в Экселе очень​ — формат ячеек​ — это не​Зная соответствующие коды из​

​ подписи данных гистограммы.​ одну, не помещая​Объединить и поместить в​ применим перенос текста​ изучим такие полезные​ строку в ячейке.​ подтягиваются из других​и​ будут выступать за​ только одной кнопки​ другими данными. Но,​ (например) или поставьте​ одного Алексея. Но​ сложно. Можно сделать​

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

​ — выравнивание -​ текстовый редактор и​ таблицы ASCII и​Конечно в ячейке можно​ содержимое в центре.​ центре​ по строкам к​ функции Microsoft Excel,​Автоматический перенос текста​ диапазонов с помощью​

1. ​«ТЕКСТ2»​ границы ячейки, то​
2. ​Enter​ что делать, если​ формат ячейки -​ он макровед.​

   ​ разбивку текста из​

   ​ переносить по словам​​ его возможности в​​ используя функцию СИМВОЛ,​​ сделать перенос строки​​Отменить объединение ячеек:​на вкладке​ столбцу D.​ как перенос текста​Выделите ячейки, которые нужно​

   

3. ​ формулы. В остальных​нужно подставить слова​ она автоматически расширится​​, с помощью этого​​ нужно перенести текст​

​ Выравнивание — Автоподбор​А вы не​ ячейки на несколько​ — ОК​ этом направлении минимальны.​

​ мы имеем возможность​​ в любом месте​

​Отменяет объединение.​Главная​Выделите ячейки, текст в​ по строкам и​ отформатировать.​ случаях использование данного​ или наборы слов,​ в высоту, а​ способа будет достигнут​ в пределах одной​ ширины (галку Переносить​ знаете nick автора​ строк по завершению​Скопировали пустую ячейку​ЗЫ Вероятно макросами​ вставить любой символ​ с помощью комбинации​Урок подготовлен для Вас​.​ которых должен отображаться​ объединение нескольких ячеек​Выберите​ способа является нерациональным,​ которые хотите перенести.​ слова станут переноситься.​ именно такой результат,​ ячейки на другую​ по словам нужно​ ?​ ввода. Макрос можно​

​ В3.​

lumpics.ru

Перенос текста в ячейке

​ можно…​​ в формулу Excel.​ клавиш ALT+Enter. Но​ командой сайта office-guru.ru​Выделенные ячейки будут объединены​ на нескольких строках.​ в одну. С​Главная​ так как имеются​ Остальные символы формулы​ Иногда приходится расширять​ который ставится.​ строку? Данную задачу​ снять) — текст​Serge_007​ прицепить на кнопку​Топнули правой мышкой​0mega​ У выше описанном​ если таких ячеек​Источник: http://www.gcflearnfree.org/office2013/excel2013/8/full​ в одну, а​

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

​: Это тоже не​

1. ​ (как здесь) или​ на F3 -​

2. ​:​​ примере вставлен символ​​ десятки лучше автоматизировать​​Автор/переводчик: Антон Андронов​​ текст разместится в​​ мы выделим ячейки​ ​ Вы сможете переносить​

   

3. ​Выравнивание​ варианты решения поставленной​Для того, чтобы результат​Чтобы подобным образом не​Горячие клавиши в Экселе​

   

​ некоторыми возможностями программы.​​А вообще-то формула​ решение (см. вложение)​ на событие Worksheet_Change.​ Специальная вставка -​Ambioz​ переноса строки, который​

Ввод разрыва строки

​ данный процесс с​Автор: Антон Андронов​ центре.​

1. ​ в столбце D.​ текст на несколько​>​

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

2. ​ Давайте разберемся, как​ изначально не правильная.​nerv​ Можно добавить удаление​ Ширина столбцов -ОК​

​, здравствуйте​

support.office.com

Перенос текста и объединение ячеек в Excel

​ соединен с обеих​ помощью простой выше​Во время создания графиков​Кнопка​Выберите команду​ строк, создавать заголовки​Обтекание текстом​Автор: Максим Тютюшев​ нажмите кнопку​ элемент, можно сразу​ ставится задачи перенести​ сделать перевод строки​ Попробуйте в AS22​: Предлагаю установить ограничение​ исходной ячейки. Подкраска​еще раз правой​row​

​ сторон оператором &​ приведенной формулы.​ и диаграмм в​Объединить и поместить в​Перенести текст​ для таблиц, вписывать​.​Примечание:​Enter​ выделить целую область.​ на новую строку​ в ячейке в​ поставить «Желание жить​ на кол-во символов​

​ разделенного текста сделана​ мышкой -специальная вставка​: 0mega как Вы​ между значениями ячеек​​ Excel часто возникает​ центре​на вкладке​ длинный текст в​

Перенос текста в Excel

​Если текст не отображается​Мы стараемся как​на клавиатуре.​ Недостаток данного варианта​

1. ​ строго определенные слова,​ Excel.​ и работать» (без​ в ячейке равное​ для удобства при​ — форматы -ОК​
2. ​ это сделали, подскажите​​ A2 и C2.​​Программа MS Excel активно​​ необходимость сделать перенос​​действует как переключатель,​
3. ​Главная​ одну строку, не​

​ целиком даже с​​ можно оперативнее обеспечивать​​Главным недостатком данного способа​ заключается в том,​

Объединение ячеек в Excel

​ а нужно только​Скачать последнюю версию​ кавычек).​ ~ 1024. Это,​ разбивке готового документа.​В ячейке F3​Ambioz​Если в ячейке не​ применяет коды из​ строки в тексте​ т.е. повторное нажатие​.​ увеличивая при этом​

​ переносами, растяните строку,​ вас актуальными справочными​ является тот факт,​ что перенос выполняется​

1. ​ уместить их в​ Excel​
2. ​Поменяйте на вот​​ а также подбор​ Разделяет текст выделенной​​ пишем формулу:​​:​​ включен режим «Перенести​
3. ​ таблицы символов стандарта​ ячейки и не​ на нее отменит​Текст будет перенесен по​

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

Дополнительные параметры объединения ячеек в Excel

​ ячейки. По умолчанию​=B3​0mega​ текст», то наличие​​ ASCII. В ней​ только. Например, чтобы​​ объединение. Удаленные данные​ строкам.​

• ​ многое другое.​ границу вниз.​​ языке. Эта страница​ в выполнении, чем​ случае, если слова​ не выходя за​
• ​ текст внутри ячейки​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕОШИБКА(ПОИСК(» «;AS22;20));AS22;ЛЕВСИМВ(AS22;ПОИСК(» «;AS22;20)))​ объединенные ячейки, обсуждалось​ установлена длинна строки​В ячейку В3​- маленько не​
• ​ символа переноса строки​​ находятся все коды​ получить лучший визуальный​ при этом не​
• ​Нажмите команду​​Очень часто содержимое не​

​Примечание:​ переведена автоматически, поэтому​
​ предыдущие варианты.​
​ не будут вмещаться​

​ её границы, то​

office-guru.ru

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

​ нажатием на клавиатуре​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕОШИБКА(ПОИСК(» «;AS22;20));»»;ПСТР(AS22;ПОИСК(» «;AS22;20);75))​ здесь http://www.planetaexcel.ru/forum.p….orum=97​ 116 символов. Можно​ записываем проверочный текст​ то. Текст надо​ будет незаметно:​ символов, которые высвечиваются​ эффект для читабельности​ восстановятся​Перенести текст​

Формула для переноса строки в ячейках Excel

​ может полностью отобразиться​  Текст будет переноситься​ ее текст может​Урок:​ в границы, к​ можно воспользоваться инструментом​ кнопки​aleksasha888​aleksasha888​ задать другую длинну.​P.S.​ распределить не в​Но на гистограммах, графиках​

​ на экране в​ подписей данных на​Для доступа к дополнительным​еще раз, чтобы​ в ячейке, т.к.​ по ширине столбца,​ содержать неточности и​Полезные функции Excel​

​ тому же разбитие​ форматирования.​Enter​: вроде заработало, но​: подскажите пожалуйста я​_Boroda_​в первоисточнике автор​ одной ячейке, а​ и диаграммах символы​ операционной системе MS​

​ графиках и диаграмме.​

Как работает функция СИМВОЛ при переносе строк в ячейке

​ параметрам объединения ячеек,​ отменить перенос.​ ее ширины не​ к которому относится​ грамматические ошибки. Для​В целом пользователь должен​ осуществляется автоматически без​Выделяем ячейку, в которой​. Но этим они​ потом опять слова​ не как не​: Тот же файл,​ не раздвигал ячейку,​ по строкам.​ переноса строк, вставленные​ Windows любых версий.​Рассмотрим пример ниже на​ нажмите стрелку рядом​При объединении двух и​ хватает. В таких​ ячейка. При изменении​ нас важно, чтобы​ сам решить, каким​ учета желания пользователя.​ текст выходит за​ добиваются только того,​ в ячейке (Z22)​ могу организовать перенос​

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

​ пределы границ. Кликаем​ что курсор перемещается​ поехали.​ текста из одной​ RANа, только все​

​ ячейки.​: вроде бы в​ либо введенные из​ ASCII содержит 255​ созданной в программе​Объединить и поместить в​ ячейка занимает место​ выбрать один из​ текста настраивается автоматически.​ вам полезна. Просим​ оптимальнее воспользоваться в​

exceltable.com

Автоматический перенос текста на другую строку

​ внутри ячейки при​​ по ней правой​ на следующую строку​
​nerv​ ячейки в другую.​ это без макросов,​Лично я считаю​ файле я уже​

​ клавиатуры (ALT+Enter) будут​​ кодов по которым​
​ Excel. Подписи оси​ центре​ объединенных, но данные​ двух вариантов: перенести​Новую строку текста можно​
​ вас уделить пару​ конкретном случае. Если​

​ помощи формул. Этот​​ кнопкой мыши. В​​ листа. Мы же​​: Как вариант макрос,​

​сам перенос по​​ формулами. К сожалению,​ что объединение -​

​ все написал ?!​​ отображаться естественным образом.​​ пронумерованы все символы​​ X содержат имена​. Появится выпадающее меню,​ при этом не​ текст по строкам​ начать в любом​

​ секунд и сообщить,​​ вы хотите только,​ вариант особенно актуален​ открывшемся списке выбираем​
​ рассмотрим варианты переноса​
​ который рассчитывает кол-во​ функциям:​
​ при изменении ширины​ это зло​
​попробую еще раз​ То есть переносом​ от 1-го и​
​ продавцов и данные​ со следующими командами:​ складываются. Вы можете​ или объединить несколько​
​ месте ячейки.​ помогла ли она​ чтобы все символы​
​ в том случае,​ пункт​ именно внутри ячейки,​ символов, которое может​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ДЛСТР(AS22)​
​ столбца обновляется не​0mega​
​подробная инструкция для​ разбивать одну текстовую​ до 255. Например,​ о продажах. Такой​
​Объединить и поместить в​ объединить любой смежный​ ячеек в одну,​
​Дважды щелкните ячейку, в​ вам, с помощью​
​ вмещались в границы​
​ если содержимое выводится​«Формат ячеек…»​
​ как очень простые,​
​ поместиться в ячейке​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ДЛСТР(AS22) получился, но​ автоматически, а через​: именно об этом​
​ Excel 2003​ строку на две​ большая (в верхнем​

​ пример подписи данных​​ центре:​ диапазон, и даже​

​ при этом изменять​ которую требуется ввести​ кнопок внизу страницы.​ ячейки, то просто​ с помощью функций,​

​.​​ так и более​ (объединенной) исходя из​

​ слово которое попало​​ F9​ я и предупреждал​задаваемый текст пишется​ и больше.​ регистре) буква А​ на гистограмме всегда​Объединяет выделенные ячейки​ все ячейки на​ ширину столбцов Вам​ разрыв строки.​ Для удобства также​ отформатируйте её нужным​ но его можно​Открывается окно форматирования. Переходим​ сложные.​ размера, типа шрифта​ в промежутке с​0mega​На этом форуме​ в ячейке В3​Ambioz​ имеет код 97.​

​ пригодиться, когда текст​​ в одну, а​ листе, при этом​ не потребуется.​Совет:​ приводим ссылку на​ образом, а лучше​ применять и в​ во вкладку​Самый простой вариант переноса​

​ и т.п.​​ 16 по 21​

​: маэстро !​​ работают очень сильные​Дублирующий текст может​: Здравствуйте, помогите, пожалуйста,​

​ Числу 9 по​​ должен компактно расположиться​ содержимое помещает в​

​ информация во всех​​При переносе текста высота​ Можно также выделить ячейку,​
​ оригинал (на английском​ всего отформатировать весь​ обычных случаях.​
​«Выравнивание»​ на другую строку,​Еще вариант: начинать​

​ символ не отображается​​Serge_007​ макрописцы. Пусть они​

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

​ диапазон. Если вы​​Отформатируйте ячейку, как указано​. В блоке настроек​ это установить курсор​ с новой строки​ не в первой(Z22)​
​: Однако задачу ТС​ берут в руки​
​ Листе .​
​Как сделать, что​ присвоен код 57.​ затенять элементы гистограммы.​Объединить по строкам:​ левой, будет удалена.​ изменяться, что позволит​ клавишу F2.​Если вы хотите отобразить​
​ хотите устроить перенос​

​ в предыдущем варианте.​​«Отображение»​ перед тем отрезком,​ и заполнять все​ не во второй​ без макросов решить​ дирижерские палочки.​Для удобства располагаем​ бы текст, достигнув​ Код символа пробела​Секрет данного решения весьма​Объединяет ячейки по​В примере ниже мы​
​ содержимому отображаться на​Дважды щелкните в ячейке​ весь текст в​ конкретных слов, то​Выделите ячейку и введите​выбираем параметр​
​ который нужно перенести,​ в одной объединенной​
​ ячейке(A23).​
​ всё-же нельзя.​

​Serge_007​​ его на этом​ конца ячейки автоматически​ – 32, а​ прост и элементарен.​

​ строкам, т.е. в​​ объединим диапазон A1:E1,​ нескольких строках. Объединение​ то место, куда​ ячейке, можно отформатировать​ наберите соответствующее сочетание​ в неё или​«Переносить по словам»​
​ а затем набрать​ ячейке.​Заранее спасибо.​Ambioz​: Штатными средствами это​

excelworld.ru

​ же Листе в​

Помогла ли Вам статья?

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

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