Czy importujesz lub wprowadzasz dane w programie Excel zawierające zera wiodące, takie jak 00123, lub duże liczby, takie jak 1234 5678 9087 6543? Są to na przykład numery PESEL, numery telefonów, numery kart kredytowych, kody produktów, numery kont lub kody pocztowe. Excel automatycznie usuwa zera wiodące i konwertuje duże liczby na notację naukową, na przykład 1,23E+15, aby umożliwić pracę nad formułami i operacjami matematycznych. W tym artykule opisano, jak zachować dane w oryginalnym formacie, który Excel traktować jako tekst.
Konwertowanie liczb na tekst podczas importowania danych tekstowych
Za Excel dodatku Get & Transform (Power Query) można formatować pojedyncze kolumny jako tekst podczas importowania danych. W tym przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych zaimportowanych z innych źródeł, takich jak XML, Sieć Web, JSON itp.
-
Kliknij kartę Dane, a następnie pozycję Z tekstu/pliku CSV obok przycisku Pobierz dane. Jeśli nie widzisz przycisku Pobierz dane, przejdź do strony Nowe zapytanie >Z pliku > Z tekstu i przejdź do pliku tekstowego, a następnie naciśnij pozycję Importuj.
-
Excel spowoduje załadowanie danych do okienka podglądu. Naciśnij pozycję Edytuj w okienku podglądu, aby załadować Edytor zapytań.
-
Jeśli którekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia > Przekształcanie >typ danych > pozycję Tekst.
Porada: Możesz zaznaczyć wiele kolumn, przytrzymując naciśnięty klawisz Ctrl i klikając lewym przyciskiem myszy.
-
Następnie kliknij przycisk Zamień bieżący w oknie dialogowym Zmienianie typu kolumny i Excel przekonwertuje zaznaczone kolumny na tekst.
-
Gdy to zrobisz, kliknij przycisk Zamknij, & załaduj, a Excel dane zapytania zostaną zwrócone do arkusza.
Jeśli Twoje dane u zostaną w przyszłości wprowadzone zmiany, możesz przejść do strony Odświeżanie >iExcel automatyczne aktualizowanie danych i stosowanie przekształceń.
W Excel 2010 i 2013 istnieją dwie metody importowania plików tekstowych i konwertowania liczb na tekst. Zalecaną metodą jest użycie dodatku Power Query, które jest dostępne po pobraniu dodatku Power Query. Jeśli nie możesz pobrać dodatku Power Query, możesz użyć Kreatora importu tekstu. W tym przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych zaimportowanych z innych źródeł, takich jak XML, Sieć Web, JSON itp.
-
Kliknij kartę Power Query na Wstążce, a następnie wybierz pozycję Pobierz dane zewnętrzne > z tekstu.
-
Excel spowoduje załadowanie danych do okienka podglądu. Naciśnij pozycję Edytuj w okienku podglądu, aby załadować Edytor zapytań.
-
Jeśli którekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia > Przekształcanie >typ danych > pozycję Tekst.
Porada: Możesz zaznaczyć wiele kolumn, przytrzymując naciśnięty klawisz Ctrl i klikając lewym przyciskiem myszy.
-
Następnie kliknij przycisk Zamień bieżący w oknie dialogowym Zmienianie typu kolumny i Excel przekonwertuje zaznaczone kolumny na tekst.
-
Gdy to zrobisz, kliknij przycisk Zamknij, & załaduj, a Excel dane zapytania zostaną zwrócone do arkusza.
Jeśli Twoje dane u zostaną w przyszłości wprowadzone zmiany, możesz przejść do strony Odświeżanie >iExcel automatyczne aktualizowanie danych i stosowanie przekształceń.
Stosowanie formatu niestandardowego do zera wiodącego
Jeśli chcesz rozwiązać ten problem tylko w skoroszycie, ponieważ nie jest używany przez inne programy jako źródło danych, możesz użyć formatu niestandardowego lub specjalnego, aby zachować zera wiodące. Działa to w przypadku kodów liczbowych zawierających mniej niż 16 cyfr. Ponadto możesz sformatować kody liczb za pomocą kresek lub innych znaków interpunkacyjnych. Aby na przykład numer telefonu był bardziej czytelny, możesz dodać kreskę między kodem międzynarodowym, kodem kraju/regionu, kodem kierunkowym, prefiksem i ostatnimi numerami.
|
Kod numeru |
Przykład |
Niestandardowy format liczb |
|---|---|---|
|
Społecznościowe |
012345678 |
000-00-0000 |
|
Phone (Telefon) |
0012345556789 |
00-0-000-000-0000 |
|
Poowiąz |
00123 |
00000 |
Czynności
-
Zaznacz komórkę lub zakres komórek, które chcesz sformatować.
-
Naciśnij klawisze Ctrl+1, aby załadować okno dialogowe Formatowanie komórek.
-
Wybierz kartę Liczby, na liście Kategoria kliknij pozycję Niestandardowe, a następnie w polu Typ wpisz format liczb, na przykład 000-00-0000 dla numeru PESZ lub 00000 w przypadku 5-cyfrowego kodu pocztowego.
Porada: Możesz również kliknąć pozycję Specjalne, a następnie wybrać pozycję Kod pocztowy ,Kod pocztowy + 4, Telefon numerlub Numer PE PESZ.
Aby uzyskać więcej informacji na temat niestandardowych kodów, zobacz Tworzenie lub usuwanie niestandardowego formatu liczb.
Uwaga: Nie spowoduje to przywrócenia zer wiodących, które zostały usunięte przed formatowaniem. Ma to wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
Stosowanie formatu przy użyciu funkcji TEKST
Możesz użyć pustej kolumny obok danych i użyć funkcji TEKST, aby przekonwertować ją na format, który chcesz.
|
Kod numeru |
Przykład (w komórce A1) |
Funkcja TEKST i nowy format |
|---|---|---|
|
Społecznościowe |
012345678 |
=TEKST(A1;"000-00-0000") |
|
Phone (Telefon) |
0012345556789 |
=TEKST(A1;"00-0-000-000-0000") |
|
Poowiąz |
00123 |
=TEKST(A1;"00000") |
Numery kart kredytowych są zaokrąglane w dół
Excel ma maksymalną dokładność do 15 cyfr znaczących, co oznacza, że dla każdej liczby zawierającej co najmniej 16 cyfr, na przykład numeru karty kredytowej, wszystkie liczby po 15 cyfrze są zaokrąglane w dół do zera. W przypadku kodów liczbowych o rozmiarze co najmniej 16 cyfr należy użyć formatu tekstowego. W tym celu możesz wykonać jedną z dwóch czynności:
-
Formatowanie kolumny jako tekstu
Zaznacz zakres danych i naciśnij klawisze Ctrl+1, aby uruchomić okno dialogowe Formatowanie > komórki. Na karcie Liczby kliknij pozycję Tekst.
Uwaga: Nie spowoduje to zmiany liczb, które zostały już wprowadzone. Ma to wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
-
Używanie znaku apostrofu
Możesz wpisać apostrof (') przed liczbą, a Excel potraktuje ją jako tekst.
Potrzebujesz dodatkowej pomocy?
Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w Społecznościach.
