Spis treści
Podziały wierszy w tej samej komórce, dodawane za pomocą skrótu klawiaturowego inny+Wchodzę to bardzo powszechna i powszechna rzecz. Czasami są tworzone przez samych użytkowników, aby dodać urody do długiego tekstu. Czasem takie transfery są dodawane automatycznie podczas rozładowywania danych z dowolnych działających programów (cześć 1C, SAP itp.) Problem w tym, że wtedy trzeba nie tylko podziwiać takie tabele, ale pracować z nimi – i wtedy te niewidoczne transfery znaków mogą być problem. I mogą się nie stać – jeśli wiesz, jak sobie z nimi poradzić.
Przyjrzyjmy się temu zagadnieniu bardziej szczegółowo.
Usuwanie przerw w wierszu przez wymianę
Jeśli musimy pozbyć się myślników, to pierwszą rzeczą, która zwykle przychodzi na myśl, jest klasyczna technika „znajdź i zamień”. Zaznacz tekst, a następnie wywołaj okno wymiany za pomocą skrótu klawiaturowego Ctrl+H lub za pośrednictwem Strona główna – Znajdź i wybierz – Zamień (Dom — Znajdź i wybierz — Zamień). Jedna niespójność – nie bardzo wiadomo, jak wejść w górne pole Znaleźć (Znajdź co) nasz niewidzialny znak łamania linii. inny+Wchodzę tutaj niestety już nie działa kopiowanie tego symbolu bezpośrednio z komórki i wklejanie go tutaj też się nie udaje.
Połączenie pomoże Ctrl+J – to jest alternatywa inny+Wchodzę w oknach dialogowych lub polach wejściowych programu Excel:
Należy pamiętać, że po umieszczeniu migającego kursora w górnym polu i naciśnięciu Ctrl+J – w samym polu nic się nie pojawi. Nie bój się – to normalne, symbol jest niewidoczny 🙂
Do dolnego pola namiastka (Zamienić) albo nic nie wpisuj, albo wpisz spację (jeśli chcemy nie tylko usunąć myślniki, ale zastąpić je spacją, aby linie nie sklejały się w jedną całość). Wystarczy nacisnąć przycisk Wymień wszystko (Zamień wszystko) a nasze myślniki znikną:
niuans: po wykonaniu zamiany wpisane za pomocą Ctrl+J niewidzialny charakter pozostaje w terenie Znaleźć i może przeszkadzać w przyszłości – nie zapomnij go usunąć poprzez umieszczenie kursora w tym polu i kilkukrotne (dla pewności) naciśnięcie klawiszy Usuń и Backspace.
Usuwanie łamania linii za pomocą formuły
Jeśli chcesz rozwiązać problem za pomocą formuł, możesz użyć wbudowanej funkcji WYDRUKOWAĆ (CZYSTY), który może wyczyścić tekst ze wszystkich niedrukowalnych znaków, w tym z niefortunnych podziałów wierszy:
Ta opcja jednak nie zawsze jest wygodna, ponieważ linie po tej operacji można ze sobą skleić. Aby temu zapobiec, musisz nie tylko usunąć myślnik, ale także zastąpić go spacją (patrz następny akapit).
Zastępowanie łamania wierszy formułą
A jeśli chcesz nie tylko usunąć, ale i zastąpić inny+Wchodzę na przykład na przestrzeni, wymagana będzie inna, nieco bardziej złożona konstrukcja:
Aby ustawić niewidoczny myślnik, używamy funkcji SYMBOL (ZWĘGLAĆ), który wyprowadza znak według jego kodu (10). A potem funkcja ZASTĄPIĆ (ZASTĄPIĆ) wyszukuje nasze myślniki w danych źródłowych i zastępuje je dowolnym innym tekstem, na przykład spacją.
Podział na kolumny według podziału wiersza
Znane dla wielu i bardzo przydatne narzędzie Tekst według kolumn z zakładki Dane (Dane — tekst do kolumn) może również świetnie działać z podziałami wierszy i dzielić tekst z jednej komórki na kilka, dzieląc go o inny+Wchodzę. Aby to zrobić, w drugim kroku kreatora musisz wybrać wariant niestandardowego znaku ogranicznika Inne (Zwyczaj) i użyj skrótu klawiaturowego, który już znamy Ctrl+J jako alternatywa inny+Wchodzę:
Jeśli Twoje dane mogą zawierać kilka podziałów wiersza z rzędu, możesz je „zwinąć”, zaznaczając pole wyboru Traktuj kolejne ograniczniki jako jeden (Traktuj kolejne ograniczniki jako jeden).
Po kliknięciu Następna (Kolejny) i przechodząc przez wszystkie trzy kroki kreatora, otrzymujemy pożądany wynik:
Należy pamiętać, że przed wykonaniem tej operacji konieczne jest wstawienie wystarczającej liczby pustych kolumn na prawo od podzielonej kolumny, aby wynikowy tekst nie nadpisał wartości (cen), które znajdowały się po prawej stronie.
Podziel na wiersze, naciskając Alt + Enter przez Power Query
Innym ciekawym zadaniem jest podzielenie tekstu wielowierszowego z każdej komórki nie na kolumny, ale na linie:
Robienie tego ręcznie zajmuje dużo czasu, jest to trudne z formułami, nie każdy może napisać makro. Ale w praktyce ten problem występuje częściej niż byśmy chcieli. Najprostszym i najłatwiejszym rozwiązaniem jest użycie do tego zadania dodatku Power Query, który jest wbudowany w program Excel od 2016 roku, a dla wcześniejszych wersji 2010-2013 można go pobrać całkowicie za darmo z witryny firmy Microsoft.
Aby załadować dane źródłowe do dodatku Power Query, musisz najpierw przekonwertować je na „inteligentną tabelę” za pomocą skrótu klawiaturowego Ctrl+T lub przyciskiem Formatuj jako tabelę Zakładka Strona główna (Strona główna — Formatuj jako tabelę). Jeśli z jakiegoś powodu nie chcesz lub nie możesz korzystać z „inteligentnych stołów”, możesz pracować z „głupimi”. W takim przypadku wystarczy wybrać oryginalny zakres i nadać mu nazwę w zakładce Formuły – Menedżer nazw – Nowość (Formuły — Menedżer nazw — Nowość).
Następnie na karcie Dane (jeśli masz Excel 2016 lub nowszy) lub na karcie Zapytanie o moc (jeśli masz Excel 2010-2013) możesz kliknąć przycisk Ze stołu/zakresu (z tabeli/zakresu)aby załadować naszą tabelę do edytora Power Query:
Po wczytaniu wybierz kolumnę z tekstem wielowierszowym w komórkach i wybierz polecenie na karcie Główne Podziel kolumnę — według ogranicznika (Strona główna — Podziel kolumnę — Według separatora):
Najprawdopodobniej Power Query automatycznie rozpozna zasadę podziału i zastąpi sam symbol #(jeśli) niewidoczny znak nowego wiersza (lf = nowy wiersz = nowy wiersz) w polu wejściowym separatora. W razie potrzeby inne znaki można wybrać z listy rozwijanej na dole okna, jeśli najpierw zaznaczysz pole Podziel ze znakami specjalnymi (Podziel według znaków specjalnych).
Aby wszystko było podzielone na wiersze, a nie kolumny – nie zapomnij przełączyć selektora Wydziwianie (wierszami) w grupie opcji zaawansowanych.
Pozostaje tylko kliknąć OK i zdobądź to, czego chcesz:
Gotowy stół można wyładować z powrotem na arkusz za pomocą polecenia Zamknij i załaduj – Zamknij i załaduj w… Zakładka Strona główna (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…).
Należy pamiętać, że korzystając z dodatku Power Query, należy pamiętać, że w przypadku zmiany danych źródłowych wyniki nie są automatycznie aktualizowane, ponieważ. to nie są formuły. Aby zaktualizować, musisz kliknąć prawym przyciskiem myszy ostatni stół na arkuszu i wybrać polecenie Zaktualizuj i zapisz (Odświeżać) lub naciśnij przycisk Aktualizacja Wszystko Zakładka Dane (Dane — Odśwież wszystko).
Makro do podziału na linie za pomocą Alt+Enter
Aby dopełnić obraz, wspomnę jeszcze o rozwiązaniu poprzedniego problemu za pomocą makra. Otwórz Edytor Visual Basic za pomocą przycisku o tej samej nazwie na karcie Deweloper (Deweloper) lub skróty klawiaturowe inny+F11. W wyświetlonym oknie wstaw nowy moduł przez menu Wstaw – moduł i skopiuj tam następujący kod:
Sub Split_By_Rows() Dim cell As Range, n As Integer Ustaw cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'określ liczbę fragmentów cell.Offset(1, 0 ).Resize(n, 1).EntireRow.Insert 'wstaw puste wiersze poniżej komórki.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'wpisz do nich dane z tablicy Ustaw komórka = cell.Offset(n + 1, 0) 'przejdź do następnej komórki Next i End Sub
Wróć do programu Excel i zaznacz komórki z tekstem wielowierszowym, który chcesz podzielić. Następnie użyj przycisku Makra Zakładka wywoływacz (Deweloper — Makra) lub skrót klawiaturowy inny+F8aby uruchomić utworzone makro, które wykona całą pracę za Ciebie:
Voila! Programiści to tak naprawdę po prostu bardzo leniwi ludzie, którzy wolą raz ciężko pracować, a potem nic nie robić 🙂
- Czyszczenie tekstu ze śmieci i dodatkowych znaków
- Zastępowanie tekstu i usuwanie spacji nierozdzielających za pomocą funkcji SUBSTITUTE
- Jak podzielić lepki tekst na części w programie Excel?