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

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

Розглянемо приклад нижче малюнку з гістограмою, створеної у програмі Excel. Підписи осі X містять імена продавців та дані про продаж. Такий приклад підпису даних на гістограмі завжди стане в нагоді, коли текст повинен компактно розташуватися так, щоб не затінювати елементи гістограми.

Секрет цього рішеннядуже простий і елементарний. Просто необхідно додати функцію СИМВОЛ у потрібне місце формули, яка генерує значення у комірках для підпису даних гістограми.

Звичайно, в комірці можна зробити перенесення рядка в будь-якому місці за допомогою комбінації клавіш ALT+Enter. Але якщо таких осередків десятки краще автоматизувати даний процесза допомогою простої вище наведеної формули.



Як працює функція СИМВОЛ при перенесенні рядків у комірці

Програма MS Excel активно застосовує коди із таблиці символів стандарту ASCII. У ній знаходяться всі коди символів, які висвічуються на екрані операційній системі MS Windows будь-яких версій. Таблиця символів стандарту ASCII містить 255 кодів за якими пронумеровані всі символи від 1-го і до 255. Наприклад, велика (у верхньому регістрі) буква А має код 97. Число 9 цієї таблиці присвоєно код 57. Код символу пробілу – 32, а код перенесення рядка має число 10. У цій таблиці містяться недруковані символи з клавіатури.

Знаючи відповідні коди з таблиці ASCII та використовуючи функцію СИМВОЛ, ми маємо можливість вставити будь-який символ у формулу Excel. У наведеному вище прикладі вставлено символ перенесення рядка, який з'єднаний з обох сторін оператором & між значеннями осередків A2 і C2.

Якщо в осередку не ввімкнено режим «Перенести текст», то наявність символу перенесення рядка буде непомітною:

Але на гістограмах, графіках та діаграмах символи перенесення рядків, вставлені за допомогою формул або введені з клавіатури (ALT+Enter) відображатимуться природним чином. Тобто перенесенням розбивати один текстовий рядок на два і більше.

Часто потрібно всередині однієї комірки 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 ці символи не працюватимуть.

Як у осередку Ексель зробити перенесення рядка? Варіанти перенесення частини слова.
Чи не досвідчені користувачі роблять перенесення як у ворді, тобто натискають клавішу enter і тут у них замість перенесення, вони просто переходять на іншу комірку. Однак слід знати, що за допомогою клавіатури слід робити це, натиснувши одночасно на клавішу alt і enter. Це один самих простих способів. Однак при постійній роботіз excel документами великого обсягу, цей спосіб не дуже ефективний.

Розглянемо, як можна за допомогою функції форматування спростити завдання перенесення тексту в одному осередку.
Цей спосіб має невелику умову для тих, хто ним користуватиметься. Справа в тому, що він зручний у тому випадку, якщо немає точності у перенесенні слів або частини слова. Тобто цей варіант гарантує перенесення тільки всередині комірки, але не гарантує, що ви з його допомогою зможете дотримуватися точності в перенесенні певних слів або літер. Опишемо, як це робити на практиці. Виділяємо необхідну комірку за допомогою курсору. Потім натискаємо праву кнопку миші і побачимо меню. У ньому шукаємо формат осередків

Наступною дією, у меню відзначаємо «переносити за словами». Поставили пташку і кнопкою ок підтвердили свої дії.

Після вищеописаних маніпуляцій ви можете спокійно працювати в комірці з потрібним вам текстом, і він автоматично піддаватиметься примусовому перенесенню слів. Цей спосіб допомагає працювати з текстами формулами ефективніше. Звісно, ​​все залежить від ваших завдань. Наприклад, вам треба, щоб такий формат осередку був не тільки у якогось певного осередку, а у більшого масиву осередків. Це легко зробити, якщо виділити потрібний масив або лист і через функцію формат осередків встановити вище описану функцію «переносити за словами». Слід пам'ятати, що слова будуть автоматично переноситися лише у випадку, якщо слово буде біля межі осередку. Розміри за умовчанням осередків мають невеликий розмір заввишки. І цей спосіб форматування розширює межі самостійно. Якщо цього не станеться, слід збільшити висоту чи ширину комірки самостійно.
Формули
Торкнемося ще один спосіб перенесення рядка в комірці. Це можна робити за допомогою формули. У Excel існує набір функцій для роботи з текстом. Ось однією з таких функцій ми скористаємося. Припустимо, ваші комірки вже відформатовані, як описано у другому варіанті. Але вам необхідно, щоб переносилося певне слово, словосполучення. І зазвичай цим варіантом користуються, якщо слова знаходяться на інших аркушах, або частина якоїсь формули. Вирішуємо це завдання таким чином: виділяємо комірку, в якій необхідно зробити конкретне перенесення слова, фрази, і вписуємо в неї формулу: (зчепити (текст1); символ (4); текст2) Текст у цій формулі передбачає, що потрібно вставити слово, або фразу. Так ви вписуєте потрібні вам два слова, вказуєте, з якого символу необхідно почати обробляти текст.

Слова потрібно в цій формулі брати в лапки, і далі вже сама формула зробить за вас перенесення потрібного вам параметра.

Ви тільки підтверджуєте натисканням enter про свою згоду на зміни.
Всі вищеописані способи дозволяють робити перенесення слів у осередку, і кожен із них підходить для вирішення певних, конкретних завдань. Два способи досить легкі та їх просто запам'ятати. Третій потребує певного часу. Але він і призначений для вирішення вужчих завдань при перенесенні тексту.

Ця інструкція познайомить Вас із трьома способами видалити повернення каретки з осередків у Excel. Ви також дізнаєтесь, як замінити символи перенесення рядка іншими символами. Усі запропоновані рішення працюють у Excel 2013, 2010, 2007 та 2003.

Перенесення рядків може з'явитися в тексті з різних причин. Зазвичай повернення каретки зустрічаються в робочій книзі, наприклад, коли текст скопійований з веб-сторінки, коли вони вже є в робочій книзі, отриманій від клієнта, або коли ми самі додаємо їх натисканням клавіш Alt+Enter.

Якою б не була причина їхньої появи, зараз перед нами стоїть завдання видалити повернення каретки, тому що вони заважають виконувати пошук фраз і призводять до безладдя у стовпчику при включенні режиму перенесення тексту.

Усі три представлені способи досить швидкі. Вибирайте той, який Вам найбільше підходить:

Примітка:Спочатку терміни "Повернення каретки" та "Переклад рядка" використовувалися при роботі на друкарських машинках і позначали дві різні операції. Допитливий читач може.

Комп'ютери та програмне забезпеченнядо роботи з текстами розроблялися з урахуванням особливостей друкарських машинок. Ось чому тепер для позначення розриву рядка використовують два різні недруковані символи: Повернення каретки(Carriage return, CR або ASCII код 13) та Переклад рядка(Line feed, LF або ASCII код 10). У Windows використовуються обидва символи разом, а в системах *NIX застосовується лише переклад рядка.

Будьте уважні:У Excel зустрічаються обидва варіанти. При імпорті з файлів .txtабо .csvдані зазвичай містять повернення каретки та переклади рядка. Коли перенесення рядка вводиться вручну натисканням Alt+Enter, Excel вставляє лише символ перекладу рядка. Якщо ж файл .csvотримано від шанувальника Linux, Unix або іншої подібної системи, то готуйтеся до зустрічі лише із символом перекладу рядка.

Видаляємо повернення каретки вручну

Плюси:Цей спосіб найшвидший.

Мінуси:Жодних додаткових плюшок

Ось так можна видалити перенесення рядків за допомогою інструмента. Знайти та замінити»:

Видаляємо перенесення рядків за допомогою формул Excel

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

У деяких випадках переноси рядків необхідно видаляти, щоб надалі використовувати текст як аргументи функцій, не вносячи зміни до вихідних осередків. Результат можна використовувати, наприклад, як аргумент функції ПЕРЕГЛЯД(LOOKUP).

Мінуси:Потрібно створити допоміжний стовпець та виконати безліч додаткових кроків.

Видаляємо перенесення рядків за допомогою макросу VBA

Плюси:Створюємо один раз – використовуємо знову та знову з будь-якою робочою книгою.

Мінуси:Потрібно хоча б базове знання VBA.

Макрос VBA з наступного прикладу видаляє повернення каретки зі всіх осередків на активному аркуші.

Sub RemoveCarriageReturns() Dim MyRange As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each MyRange In ActiveSheet.UsedRange If 0< InStr(MyRange, Chr(10)) Then MyRange = Replace(MyRange, Chr(10), "") End If Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

Якщо ви не дуже близько знайомі з VBA, рекомендую вивчити статтю про те,

В Екселі існує відразу кілька варіантів перенести текст всередині одного осередку.

1 спосіб

Потрібно скористатися інструментом форматування комірки.

1) Клацніть правою кнопкоюмиші на потрібній комірці або відразу на кількох осередках, в яких потрібно переносити текст. У контекстному менюВиберіть «Формат осередків».

2) Відкривається вікно форматування. Потрібно відкрити вкладку «Вирівнювання» та у блоці «Відображення» поставити галочку на пункті «Переносити за словами».

3) Залишилося натиснути кнопку «OK». Текст буде перенесено і відображатиметься не в одному рядку, а в кількох.

2 спосіб

1) Виділіть потрібну комірку, для цього достатньо клацнути на ній лівою кнопкою миші.

2) На панелі інструментів програми Excelнатисніть кнопку «Перенесення тексту».

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

3 спосіб

1) Перейдіть в режим редагування тексту, для цього клацніть двічі лівою кнопкою миші всередині комірки. Курсор у своїй потрібно встановити перед частиною тексту, яку потрібно перенести.

2) Тепер наберіть комбінацію клавіш "Alt" + "Enter" на клавіатурі. Текст буде поділено.

3) Щоб побачити остаточний результат, достатньо вийти з режиму редагування осередку.

Для виходу з режиму редагування можна натиснути клавішу "Enter" або натиснути 1 раз лівою кнопкою миші на будь-якій іншій комірці таблиці.