Czy kiedykolwiek importujesz lub wprowadzasz w programie Excel dane zawierające zera wiodące, takie jak 00123, lub duże liczby, takie jak 1234 5678 9087 6543? Przykładowe numery pesel, numery telefonów, numery kart kredytowych, kody produktów, numery kont lub kody pocztowe. Program 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 matematycznymi. W tym artykule opisano, jak zachować dane w oryginalnym formacie, który program Excel traktuje jako tekst.
Konwertowanie liczb na tekst podczas importowania danych tekstowych
Formatowanie pojedynczych kolumn jako tekstu podczas importowania danych za pomocą funkcji Przekształcanie & (Power Query) w programie Excel. W takim przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych importowanych 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 pozycji Nowe zapytanie > Z pliku > Z tekstu i przejdź do pliku tekstowego, a następnie naciśnij klawisz Importuj.
-
Program Excel załaduje dane do okienka podglądu. Naciśnij klawisz Edytuj w okienku podglądu, aby załadować Edytor Power Query.
-
Jeśli jakiekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia główne > Przekształcanie > typ danych , > wybierz pozycję Tekst.
Porada: Możesz zaznaczyć wiele kolumn za pomocą klawiszy Ctrl i kliknięcia lewym przyciskiem myszy.
-
Następnie kliknij pozycję Zamień bieżący w oknie dialogowym Zmienianie typu kolumny , a program Excel przekonwertuje zaznaczone kolumny na tekst.
-
Po zakończeniu kliknij przycisk Zamknij & Załaduj, a program Excel zwróci dane zapytania do arkusza.
Jeśli dane zmienią się w przyszłości, możesz przejść do pozycji Dane > Odśwież, a program Excel automatycznie zaktualizuje dane i zastosuje przekształcenia za Ciebie.
W programach Excel 2010 i 2013 istnieją dwie metody importowania plików tekstowych i konwertowania liczb na tekst. Zalecaną metodą jest użycie Power Query, który jest dostępny po pobraniu dodatku Power Query. Jeśli nie możesz pobrać Power Query dodatku, możesz użyć Kreatora importu tekstu. W takim przypadku importujemy plik tekstowy, ale kroki przekształcania danych są takie same w przypadku danych importowanych 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.
-
Program Excel załaduje dane do okienka podglądu. Naciśnij klawisz Edytuj w okienku podglądu, aby załadować Edytor Power Query.
-
Jeśli jakiekolwiek kolumny muszą zostać przekonwertowane na tekst, zaznacz kolumnę do przekonwertowania, klikając nagłówek kolumny, a następnie przejdź do pozycji Narzędzia główne > Przekształcanie > typ danych , > wybierz pozycję Tekst.
Porada: Możesz zaznaczyć wiele kolumn za pomocą klawiszy Ctrl i kliknięcia lewym przyciskiem myszy.
-
Następnie kliknij pozycję Zamień bieżący w oknie dialogowym Zmienianie typu kolumny , a program Excel przekonwertuje zaznaczone kolumny na tekst.
-
Po zakończeniu kliknij przycisk Zamknij & Załaduj, a program Excel zwróci dane zapytania do arkusza.
Jeśli dane zmienią się w przyszłości, możesz przejść do pozycji Dane > Odśwież, a program Excel automatycznie zaktualizuje dane i zastosuje przekształcenia za Ciebie.
Stosowanie formatu niestandardowego w celu zachowania zer wiodących
Jeśli chcesz rozwiązać ten problem tylko w skoroszycie, ponieważ nie jest on używany przez inne programy jako źródło danych, możesz użyć niestandardowego lub specjalnego formatu, aby zachować zera wiodące. Działa to w przypadku kodów liczbowych zawierających mniej niż 16 cyfr. Ponadto kody liczb można formatować za pomocą kresek lub innych znaków interpunkcyjnych. Aby na przykład zwiększyć czytelność numeru telefonu, możesz dodać kreskę między kodem międzynarodowym, kodem kraju/regionu, numerem kierunkowym, prefiksem i kilkoma ostatnimi numerami.
|
Kod numeru |
Przykład |
Niestandardowy format liczb |
|---|---|---|
|
Społecznych |
012345678 |
000-00-0000 |
|
Phone (Telefon) |
0012345556789 |
00-0-000-000-00000 |
|
Pocztowych |
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ę Liczba , na liście Kategoria kliknij pozycję Niestandardowe , a następnie w polu Typ wpisz format liczb, na przykład 000-00-0000 dla kodu pesel lub 00000 dla pięciocyfrowego kodu pocztowego.
Porada: Możesz również kliknąć pozycję Specjalne, a następnie wybrać pozycję Kod pocztowy, Kod pocztowy + 4, Numer telefonu lub Numer PESEL.
Aby uzyskać więcej informacji na temat kodów niestandardowych, zobacz Tworzenie lub usuwanie niestandardowego formatu liczb.
Uwaga: Nie spowoduje to przywrócenia zer wiodących usuniętych przed formatowaniem. Będzie to miało wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
Stosowanie formatu za pomocą funkcji TEKST
Możesz użyć pustej kolumny obok danych i przekonwertować ją na odpowiedni format za pomocą funkcji TEKST .
|
Kod numeru |
Przykład (w komórce A1) |
FUNKCJA TEKST i nowy format |
|---|---|---|
|
Społecznych |
012345678 |
=TEKST(A1;"000-00-0000") |
|
Phone (Telefon) |
0012345556789 |
=TEKST(A1;"00-0-000-000-000-0000") |
|
Pocztowych |
00123 |
=TEKST(A1;"00000") |
Numery kart kredytowych są zaokrąglane w dół
Maksymalna dokładność programu Excel wynosi 15 cyfr znaczących, co oznacza, że w przypadku dowolnej liczby zawierającej co najmniej 16 cyfr, takiej jak numer karty kredytowej, wszystkie liczby przeszłe przez 15 cyfrę są zaokrąglane w dół do zera. W przypadku kodów liczbowych zawierających 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órek >. Na karcie Liczby kliknij pozycję Tekst.
Uwaga: Nie spowoduje to zmiany liczb, które zostały już wprowadzone. Będzie to miało wpływ tylko na liczby wprowadzone po zastosowaniu formatu.
-
Używanie apostrofu
Przed liczbą możesz wpisać apostrof ('), a program Excel potraktuje go 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.
