Съдържание
Прекъсвания на редове в една и съща клетка, добавени с помощта на клавишна комбинация Друг+Въведете е много често срещано и обичайно нещо. Понякога те се правят от самите потребители, за да добавят красота към дългия текст. Понякога такива трансфери се добавят автоматично при разтоварване на данни от всякакви работещи програми (здравей 1C, SAP и т.н.) Проблемът е, че тогава трябва не само да се възхищавате на такива таблици, но и да работите с тях - и тогава тези невидими трансфери на знаци могат да бъдат проблем. А може и да не станат – ако знаете как да боравите правилно с тях.
Нека да разгледаме този въпрос по-подробно.
Премахване на нови редове чрез замяна
Ако трябва да се отървем от тиретата, тогава първото нещо, което обикновено идва на ум, е класическата техника „намери и замени“. Изберете текста и след това извикайте заместващия прозорец с клавишна комбинация Ctrl+H или чрез Начало – Търсене и избор – Замяна (Начало — Търсене и избор — Замяна). Едно несъответствие – не е много ясно как се въвежда в горното поле Да намеря (Намери какво) нашият невидим знак за прекъсване на ред. Друг+Въведете тук, за съжаление, вече не работи, копирането на този символ директно от клетката и поставянето му тук също е неуспешно.
Комбинация ще помогне Ctrl+J – това е алтернативата Друг+Въведете в диалогови прозорци или полета за въвеждане на Excel:
Моля, имайте предвид, че след като поставите мигащия курсор в горното поле и натиснете Ctrl+J – нищо няма да се появи в самото поле. Не се плашете – това е нормално, символът е невидим 🙂
Към долното поле заместител (Замени с) или да не въвеждаме нищо, или да въвеждаме интервал (ако искаме не само да премахнем тиретата, но и да ги заменим с интервал, за да не се слепват редовете в едно цяло). Просто натиснете бутона Сменете всичко (Замени всички) и нашите тирета ще изчезнат:
нюанс: след извършване на замяната, въведена с Ctrl+J невидим герой остава в полето Да намеря и може да попречи в бъдеще - не забравяйте да го изтриете, като поставите курсора в това поле и няколко пъти (за надеждност) натиснете клавишите Изтрий и Backspace.
Премахване на нови редове с формула
Ако трябва да разрешите проблема с формули, тогава можете да използвате вградената функция PRINT (ЧИСТ), който може да изчисти текста от всички непечатаеми знаци, включително нашите злополучни прекъсвания на редове:
Тази опция обаче не винаги е удобна, тъй като линиите след тази операция могат да бъдат залепени заедно. За да предотвратите това, трябва не само да премахнете тирето, но и да го замените с интервал (вижте следващия параграф).
Замяна на нов ред с формула
И ако искате не просто да изтриете, а да замените Друг+Въведете на, например, пространство, тогава ще е необходима друга, малко по-сложна конструкция:
За да зададем невидимо тире, използваме функцията СИМВОЛ (CHAR), който извежда знак по своя код (10). И тогава функцията ЗАМЕСТИТЕЛ (ЗАМЕСТВАНЕ) търси нашите тирета в изходните данни и ги замества с всеки друг текст, например с интервал.
Разделяне на колони чрез нов ред
Познат на мнозина и много удобен инструмент Текст по колони от раздела Дата (Данни — Текст към колони) също може да работи чудесно с прекъсвания на редове и да разделя текста от една клетка на няколко, като го разделя по Друг+Въведете. За да направите това, във втората стъпка на съветника трябва да изберете вариант на персонализирания разделителен знак Други (Персонализиран) и използваме клавишната комбинация, която вече познаваме Ctrl+J като алтернатива Друг+Въведете:
Ако вашите данни може да съдържат няколко нови реда подред, тогава можете да ги „свиете“, като поставите отметка в квадратчето Третирайте последователните разделители като един (Третирайте последователните разделители като един).
След като кликнете върху Напред (Следващия) и преминавайки през всичките три стъпки на съветника, получаваме желания резултат:
Моля, имайте предвид, че преди да извършите тази операция, е необходимо да вмъкнете достатъчен брой празни колони отдясно на разделената колона, така че полученият текст да не презаписва стойностите (цените), които са били отдясно.
Разделете на редове с Alt + Enter чрез Power Query
Друга интересна задача е да разделите многоредовия текст от всяка клетка не на колони, а на редове:
Отнема много време да се направи това ръчно, трудно е с формули, не всеки може да напише макрос. Но на практика този проблем се среща по-често, отколкото бихме искали. Най-простото и лесно решение е да използвате за тази задача добавката Power Query, която е вградена в Excel от 2016 г., а за по-ранни версии 2010-2013 може да бъде изтеглена напълно безплатно от сайта на Microsoft.
За да заредите изходните данни в Power Query, първо трябва да ги конвертирате в „интелигентна таблица“ с клавишна комбинация Ctrl+T или чрез бутон Форматирайте като таблица етикет Начало (Начало — Форматиране като таблица). Ако по някаква причина не искате или не можете да използвате „умни таблици“, тогава можете да работите с „глупави“. В този случай просто изберете оригиналния диапазон и му дайте име в раздела Формули – Мениджър на имена – Ново (Формули — Мениджър на имена — Ново).
След това на табл Дата (ако имате Excel 2016 или по-нова версия) или в раздела Запитване за захранване (ако имате Excel 2010-2013) можете да кликнете върху бутона От таблица/обхват (От таблица/обхват)за да заредите нашата таблица в редактора на Power Query:
След зареждане изберете колоната с многоредов текст в клетките и изберете командата в раздела Основни Разделяне на колона – чрез разделител (Начало — Разделяне на колона — По разделител):
Най-вероятно Power Query автоматично ще разпознае принципа на разделяне и ще замени самия символ #(ако) невидим знак за подаване на ред (lf = подаване на ред = подаване на ред) в полето за въвеждане на разделител. Ако е необходимо, други символи могат да бъдат избрани от падащия списък в долната част на прозореца, ако първо поставите отметка в квадратчето Разделяне със специални знаци (Разделено по специални знаци).
Така че всичко е разделено на редове, а не на колони - не забравяйте да превключите селектора Редове (По редове) в групата с разширени опции.
Остава само да кликнете върху OK и вземете каквото искате:
Готовата маса може да бъде разтоварена обратно върху листа с помощта на командата Затворете и заредете – Затворете и заредете в... етикет Начало (Начало — Затвори&Зареди — Затвори&Зареди в...).
Важно е да се отбележи, че когато използвате Power Query, трябва да запомните, че когато изходните данни се променят, резултатите не се актуализират автоматично, т.к. това не са формули. За да актуализирате, трябва да щракнете с десния бутон върху финалната маса на листа и да изберете командата Актуализиране и запазване (Опресняване) или натиснете бутона Актуализиране All етикет Дата (Данни — Обновяване на всички).
Макрос за разделяне на редове с 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за да стартирате създадения макрос, който ще свърши цялата работа вместо вас:
Ето! Програмистите всъщност са просто много мързеливи хора, които предпочитат да работят усилено веднъж и след това да не правят нищо 🙂
- Почистване на текст от боклуци и допълнителни знаци
- Замяна на текст и премахване на неразделени интервали с функцията SUBSTITUTE
- Как да разделя лепкав текст на части в Excel