зміст
Розриви рядків у одній клітинці, додані за допомогою комбінації клавіш інший+ Що натомість? Створіть віртуальну версію себе у це дуже звична і поширена річ. Іноді їх роблять самі користувачі, щоб додати краси довгому тексту. Іноді такі передачі додаються автоматично при вивантаженні даних з будь-яких робочих програм (привіт 1С, SAP і т.д.). Проблема в тому, що потім такими таблицями потрібно не тільки милуватися, а й працювати з ними – і тоді ці невидимі передачі символів можуть бути проблема. А можуть і не стати – якщо знати, як з ними правильно поводитися.
Давайте розберемося в цьому питанні детальніше.
Видалення розривів рядків шляхом заміни
Якщо нам потрібно позбутися від переносів, то перше, що зазвичай спадає на думку, це класична техніка «знайти і замінити». Виділіть текст і викликайте вікно заміни за допомогою комбінації клавіш Ctrl+H або за допомогою Головна – Знайти та вибрати – Замінити (Домашня сторінка — Знайти&Вибрати — Замінити). Одна неузгодженість - не дуже зрозуміло, як ввести у верхньому полі Знайти (Знайти що) наш невидимий символ розриву рядка. інший+
Що натомість? Створіть віртуальну версію себе у
тут, на жаль, він більше не працює, скопіювати цей символ безпосередньо з клітинки та вставити його сюди також не вдається.
Допоможе комбінація Ctrl+J – це альтернатива інший+ Що натомість? Створіть віртуальну версію себе у у діалогових вікнах або полях введення Excel:
Зверніть увагу, що після того, як ви помістили миготливий курсор у верхнє поле та натисніть Ctrl+J – у самому полі нічого не з’явиться. Не лякайтеся – це нормально, символ непомітний 🙂
До нижнього поля Замінити (Замінити) або нічого не вводити, або вводити пробіл (якщо ми хочемо не тільки прибрати переноси, а замінити їх пробілом, щоб рядки не злипалися в єдине ціле). Просто натисніть кнопку Замінити все (Замінити все) і наші дефіси зникнуть:
Nuance: після виконання заміни введено с Ctrl+J невидимий персонаж залишається в полі Знайти і може заважати в майбутньому – не забудьте видалити, навівши курсор на це поле і кілька разів (для надійності) натиснувши клавіші видаляти и Повернення на одну позицію.
Видалення розривів рядків за допомогою формули
Якщо вам потрібно вирішити задачу за допомогою формул, то ви можете скористатися вбудованою функцією ДРУК (ЧИСТИЙ), який може очистити текст від усіх недрукованих символів, включаючи наші злощасні розриви рядків:
Цей варіант, правда, не завжди зручний, оскільки лінії після цієї операції можна склеїти. Щоб цього не сталося, потрібно не тільки прибрати дефіс, але й замінити його пробілом (див. наступний параграф).
Заміна розривів рядків формулою
А якщо хочеться не просто видалити, а замінити інший+ Що натомість? Створіть віртуальну версію себе у на, наприклад, просторі, то знадобиться інша, трохи більш складна конструкція:
Щоб встановити невидимий дефіс, ми використовуємо функцію СИМВОЛ (CHAR), який виводить символ за своїм кодом (10). А потім функція ЗАМІННИК (ЗАМІНА) шукає наші дефіси у вихідних даних і замінює їх на будь-який інший текст, наприклад, на пробіл.
Поділ на колонки за допомогою розриву рядка
Знайомий багатьом і дуже зручний інструмент Текст по колонках із вкладки дані (Дані — текст у стовпці) також може чудово працювати з розривами рядків і розділяти текст з однієї клітинки на кілька, розбиваючи його інший+ Що натомість? Створіть віртуальну версію себе у . Для цього на другому кроці майстра потрібно вибрати варіант власного символу роздільника Інше (Користувацький) і використовуйте комбінацію клавіш, яку ми вже знаємо Ctrl+J як альтернатива інший+ Що натомість? Створіть віртуальну версію себе у :
Якщо ваші дані можуть містити кілька розривів рядків поспіль, ви можете «згорнути» їх, увімкнувши прапорець Розглядайте послідовні роздільники як один (Розглядати послідовні роздільники як один).
Натиснувши на МАЙБУТНІ (Далі) і пройшовши всі три кроки майстра, ми отримаємо бажаний результат:
Зверніть увагу, що перед виконанням цієї операції необхідно вставити достатню кількість порожніх стовпців праворуч від розділеного стовпця, щоб отриманий текст не перезаписував значення (ціни), які були праворуч.
Розбийте на рядки за допомогою Alt + Enter через Power Query
Ще одне цікаве завдання - розділити багаторядковий текст з кожної комірки не на стовпці, а на рядки:
Це займає багато часу вручну, важко з формулами, не кожен може написати макрос. Але на практиці ця проблема зустрічається частіше, ніж хотілося б. Найпростішим і найпростішим рішенням є використання для цього завдання надбудови Power Query, яка вбудована в Excel з 2016 року, а для більш ранніх версій 2010-2013 її можна завантажити абсолютно безкоштовно з сайту Microsoft.
Щоб завантажити вихідні дані в Power Query, потрібно спочатку перетворити їх на «розумну таблицю» за допомогою комбінації клавіш Ctrl+T або кнопкою Відформатувати як таблицю таб Головна (Домашня сторінка — форматувати як таблицю). Якщо з якихось причин ви не хочете або не можете використовувати «розумні таблиці», то можете працювати з «дурними». У цьому випадку просто виберіть вихідний діапазон і дайте йому назву на вкладці Формули – Менеджер імен – Нове (Формули — Менеджер імен — Нове).
Після цього на табл дані (якщо у вас Excel 2016 або новішої версії) або на вкладці PowerQuery (якщо у вас Excel 2010-2013), ви можете натиснути на кнопку З таблиці/діапазону (З таблиці/діапазону)щоб завантажити нашу таблицю в редактор Power Query:
Після завантаження виділіть стовпець з багаторядковим текстом у комірках і виберіть команду на вкладці Основне Розділити стовпець – роздільником (Домашня сторінка — Розділити стовпець — За роздільником):
Швидше за все, Power Query автоматично розпізнає принцип ділення і підставить сам символ #(якщо) невидимий символ переводу рядка (lf = перевод рядка = перевод рядка) у поле введення роздільника. Якщо необхідно, інші символи можна вибрати зі спадного списку внизу вікна, якщо попередньо поставити прапорець Розділ зі спеціальними символами (Розділений спеціальними символами).
Щоб все було розділено на рядки, а не на стовпці – не забудьте перемкнути селектор ряди (По рядках) у групі додаткових параметрів.
Залишається лише натиснути OK і отримай те, що хочеш:
Готову таблицю можна вивантажити назад на аркуш за допомогою команди Закрийте та завантажте – Закрийте та завантажте в… таб Головна (Додому — Закрити&Завантажити — Закрити&Завантажити до…).
Важливо відзначити, що при використанні Power Query необхідно пам'ятати, що при зміні вихідних даних результати не оновлюються автоматично, т.к. це не формули. Для оновлення необхідно клацнути правою кнопкою миші на кінцевій таблиці на аркуші та вибрати команду Update & Save (Оновити) або натисніть кнопку Оновити всі таб дані (Дані — оновити все).
Макрос поділу на рядки Alt+Enter
Для повноти картини згадаємо також рішення попередньої задачі за допомогою макросу. Відкрийте редактор Visual Basic за допомогою однойменної кнопки на вкладці Розробник (розробник) або комбінації клавіш інший+F11. У вікні, що з’явиться, вставте новий модуль через меню Вставка – модуль і скопіюйте туди наступний код:
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)) 'визначати кількість фрагментів cell.Offset(1, 0) ).Resize(n, 1).EntireRow.Insert 'вставити порожні рядки під клітинку.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'ввести в них дані з масиву Set cell = cell.Offset(n) + 1, 0) 'перейти до наступної комірки Next i End Sub
Поверніться до Excel і виберіть клітинки з багаторядковим текстом, який потрібно розділити. Потім скористайтеся кнопкою Макрос таб розробник (Розробник — Макроси) або комбінацію клавіш інший+F8щоб запустити створений макрос, який зробить всю роботу за вас:
Вуаля! Програмісти, насправді, просто дуже ліниві люди, які воліють один раз попрацювати, а потім нічого не робити 🙂
- Очищення тексту від сміття та зайвих символів
- Заміна тексту та видалення нерозривних пробілів за допомогою функції ЗАМІНА
- Як розділити закріплений текст на частини в Excel