Як в excel зробити перенесення тексту в осередку

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

Це дуже часта завдання і вирішується вона дуже просто - для перенесення тексту на новий рядок всередині одного осередку Excel необхідно натиснути ALT + ENTER (Затискаєте клавішу ALT, потім не відпускаючи її, натиснути клавішу ENTER)

Як перенести текст на новий рядок в Excel за допомогою формули

Іноді потрібно зробити перенесення рядки не разово, а за допомогою функцій в Excel. Ось як в цьому прикладі на малюнку. Ми вводимо ім`я, прізвище та по батькові і воно автоматично збирається в осередку A6

Відео: Перенесення тексту в осередках Excel

Для початку нам необхідно зчепити текст в осередках A1 і B1 (A1B1), A2 і B2 (A2B2), A3 і B3 (A3B3)

Після цього об`єднаємо всі ці пари, але так само нам необхідно між цими парами поставити символ (код) розриву рядків. Є спеціальна таблиця знаків (таблиця є в кінці цієї статті), які можна вивести в Excel за допомогою спеціальної функції СИМВОЛ (число), де число це число від 1 до 255, що визначає певний знак.
Наприклад, якщо прописати = СИМВОЛ (169), то ми отримаємо знак копірайту ©

Нам же потрібно знак розриву рядків, він відповідає порядковому номеру 10 - це треба запам`ятати.
Код (символ) розриву рядків - 10
Отже перенесення рядка в Excel у вигляді функції буде виглядати ось так СИМВОЛ (10)

Отже, в осередку A6 пропишемо формулу

=A1B1СИМВОЛ (10)A2B2СИМВОЛ (10)A3B3

У підсумку ми повинні отримати потрібний нам результат.
Зверніть увагу! Щоб перенос рядка коректно відображався необхідно включити «перенесення по рядках» у властивостях осередку.
Для цього виділіть потрібну нам осередок (ячейки), натисніть на праву кнопку миші і виберіть "Формат ячеек ..."

У вікні, у вкладці «Вирівнювання» необхідно поставити галочку навпроти «Переносити за словами» як зазначено на зображенні, інакше перенесення рядків в Excel немає буде коректно відображатися за допомогою формул.

Як в Excel замінити знак переносу на інший символ і назад за допомогою формули

Можна, можливо поміняти символ перенесення на будь-який інший знак, наприклад на пробіл, за допомогою текстової функції ПІДСТАВИТИ в Excel

Розглянемо на прикладі, що на зображенні вище. Отже, в осередку B1 прописуємо функцію ПІДСТАВИТИ:

= ПІДСТАВИТИ (A1-СИМВОЛ (10) - "")

A1 - це наш текст з перенесенням рядка;
СИМВОЛ (10) - це перенесення рядка (ми розглядали це трохи вище в даній статті);
"" - це пробіл, так як ми міняємо перенесення рядка на пробіл

Якщо потрібно виконати зворотну операцію - поміняти пробіл на знак (символ) перенесення, то функція буде виглядати відповідно:

= ПІДСТАВИТИ (A1- "" -символ (10))

Нагадую, щоб перенесення рядків правильно відбивався, необхідно у властивостях осередків, в розділі «Вирівнювання» вказати «Переносити по рядках».

Як поміняти знак перенесення на пробіл і назад в Excel за допомогою ПОШУК - ЗАМІНА

Бувають випадки, коли формули використовувати незручно і потрібно зробити заміну швидко. Для цього скористаємося Пошуком і Заміною. Виділяємо наш текст і натискаємо CTRL + H, з`явиться наступне вікно.

Якщо нам необхідно поміняти перенесення рядка на пробіл, то в рядку «Знайти» необхідно ввести перенесення рядка, для цього встаньте в поле «Знайти», потім натисніть на кнопку ALT, не відпускаючи її наберіть на клавіатурі 010 - це код перенесення рядка, він не буде видно в даному полі.

Після цього в поле «Замінити на» поставте пробіл або будь-який інший символ на який вам необхідно поміняти і натисніть «Замінити» або «Замінити все».

Відео: Як зробити автоматичне перенесення слів в Excel

До речі, в Word це реалізовано більш наочно.

Як поміняти перенесення рядка на пробіл або навпаки в Excel за допомогою VBA

Розглянемо приклад для виділених осередків. Тобто ми виділяємо необхідні осередки і запускаємо макрос

Відео: Перенесення в Excel. Як перенести текст в осередку Excel

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

Sub ПробелиНаПереноси ()
For Each cell In Selection
cell.Value = Replace (cell.Value, Chr (32), Chr (10))
Next
End Sub

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

Sub ПереносиНаПробели ()
For Each cell In Selection
cell.Value = Replace (cell.Value, Chr (10), Chr (32))
Next
End Sub

Код дуже простий Chr (10) - це перенесення рядка, Chr (32) - це пробіл. Якщо потрібно поміняти на будь-який інший символ, то замінюєте просто номер коду, відповідний необхідному символу.

Коди символів для Excel

Нижче на зображенні позначені різні символи і відповідні їм коди, кілька стовпців - це різний шрифт. Для збільшення зображення, клікніть по картинці.