Jak dodać wiodące zera w programie Excel

W tym samouczku omówiono różne metody dodawania lub utrzymywania wiodących zer, a także usuwania wiodących zer w programie Excel.

Za każdym razem, gdy wprowadzasz lub importujesz liczby z co najmniej jednym wiodącym zerem, takim jak 000652, program Excel automatycznie usuwa te zera, a w komórkach pojawia się tylko sama liczba (652). Dzieje się tak, ponieważ zera wiodące nie są potrzebne do obliczeń i nie są liczone.

Jednak zdarzają się sytuacje, w których te wiodące zera są niezbędne, na przykład podczas wprowadzania numerów identyfikacyjnych, numerów telefonów, numerów kart kredytowych, kodów produktów lub kodów pocztowych itp. Na szczęście Excel daje nam kilka sposobów dodawania lub utrzymywania wiodących zer w komórkach. W tym artykule pokażemy różne sposoby dodawania lub utrzymywania wiodących zer oraz usuwania wiodących zer.

Dodawanie wiodących zer w programie Excel

Zasadniczo istnieją 2 metody dodawania wiodących zer: jedna, sformatuj liczbę jako „Tekst”; po drugie, użyj niestandardowego formatowania, aby dodać wiodące zera. Metoda, której chcesz użyć, może zależeć od tego, co chcesz zrobić z numerem.

Możesz chcieć dodać wiodące zero, gdy wprowadzasz unikalne numery identyfikacyjne, numery kont, numery ubezpieczenia społecznego lub kody pocztowe itp. Ale nie będziesz używać tych liczb do obliczeń lub funkcji, więc najlepiej je przekonwertować numery na tekst. Nigdy nie sumowałbyś ani nie uśredniał numerów telefonów ani numerów kont.

Istnieje kilka sposobów dodawania lub uzupełniania zer przed liczbami, formatując je jako tekst:

  • Zmiana formatu komórki na Tekst
  • Dodanie apostrofu (‘)
  • Korzystanie z funkcji TEKST
  • Korzystanie z funkcji REPT/LEN
  • Użyj funkcji CONCATENATE/operatora ampersand (&)
  • Korzystanie z funkcji PRAWO

Zmiana formatu komórki na tekst

Jest to jeden z najprostszych sposobów dodawania wiodących zer do swoich liczb. Jeśli zamierzasz tylko wpisywać liczby i chcesz zachować wiodące zera podczas pisania, to jest to metoda dla Ciebie. Zmieniając format komórki z Ogólnego lub Liczbowego na Tekst, możesz zmusić program Excel do traktowania liczb jako wartości tekstowych, a wszystko, co wpiszesz w komórce, pozostanie dokładnie takie samo. Oto jak to robisz:

Wybierz komórki, w których chcesz dodać wiodące zera. Przejdź do zakładki „Strona główna”, kliknij pole rozwijane „Format” w grupie Liczby i wybierz „Tekst” z opcji formatu.

Teraz, gdy wpiszesz swoje liczby, Excel nie usunie z nich żadnego wiodącego zera.

Możesz zobaczyć mały zielony trójkąt (wskaźnik błędu) w lewym górnym rogu komórki, a po wybraniu tej komórki wyświetli się znak ostrzegawczy wskazujący, że zapisałeś numer jako tekst.

Aby usunąć komunikat o błędzie, wybierz komórkę (komórki), kliknij znak ostrzegawczy, a następnie wybierz z listy „Ignoruj ​​błąd”.

Możesz także wpisać numery telefonów ze spacją lub łącznikiem między liczbą, Excel automatycznie potraktuje te numery jako tekst.

Korzystanie z prowadzenia Apostrof ( ‘ )

Innym sposobem dodawania wiodących zer w programie Excel jest dodanie apostrofu (‘) na początku liczby. Zmusi to program Excel do wprowadzenia liczby jako tekstu.

Wystarczy wpisać apostrof przed cyframi i nacisnąć „Enter”. Excel pozostawi początkowe zera nienaruszone, ale znak (‘) nie będzie widoczny w arkuszu, chyba że wybierzesz komórkę.

Korzystanie z funkcji tekstu

Powyższa metoda dodaje zera do liczb podczas ich wpisywania, ale jeśli masz już listę liczb i chcesz dodać przed nimi wiodące zera, to funkcja TEKST jest odpowiednią metodą dla Ciebie. Funkcja TEKST umożliwia konwersję liczb na ciągi tekstowe podczas stosowania niestandardowego formatowania.

Składnia funkcji TEKST:

= TEKST(wartość; format_tekst)

Gdzie,

  • wartość - Jest to wartość liczbowa, którą należy przekonwertować na tekst i zastosować formatowanie.
  • format_tekst – to format, który chcesz zastosować.

Dzięki funkcji TEKST możesz określić, ile cyfr powinien mieć Twój numer. Na przykład, jeśli chcesz, aby liczby miały 8 cyfr, wpisz 8 zer w drugim argumencie funkcji: „00000000”. Jeśli masz 6-cyfrową liczbę w komórce, funkcja ręcznie doda 2 wiodące zera, a jeśli masz 2-cyfrowe liczby, takie jak 56, reszta będzie zerami (00000056).

Na przykład, aby dodać wiodące zera i uczynić liczby sześciocyfrowe, użyj poniższej formuły:

=TEKST(A2;"000000")

Ponieważ w drugim argumencie formuły mamy 6 zer, funkcja konwertuje ciąg liczbowy na ciąg tekstowy i dodaje 5 wiodących zer, aby ciąg miał długość 6 cyfr.

Notatka: Proszę pamiętać o umieszczeniu w funkcji kodów formatów w podwójnych cudzysłowach.

Teraz możesz zastosować tę samą formułę do pozostałych komórek, przeciągając uchwyt wypełniania. Jak widać, funkcja zamienia liczby na teksty i dodaje wiodące zera do liczb tak, aby łączna liczba cyfr wynosiła 6.

Funkcja TEKST zawsze zwróci wartość jako ciąg tekstowy, a nie liczbę, więc nie będziesz mógł ich używać w obliczeniach arytmetycznych, ale nadal możesz ich używać w formułach wyszukiwania, takich jak WYSZUKAJ.PIONOWO lub INDEKS/PODZIEL, aby pobrać szczegóły produkt przy użyciu identyfikatorów produktów.

Korzystanie z funkcji CONCATENATE/operatora ampersand (&)

Jeśli chcesz dodać stałą liczbę zer wiodących przed wszystkimi liczbami w kolumnie, możesz użyć funkcji CONCATENATE lub operatora ampersand (&).

Składnia funkcji CONCATENATE:

= CONCATENATE(tekst1, [tekst2];...)

Gdzie,

Tekst 1 - Liczba zer do wstawienia przed liczbą.

tekst2 – Oryginalna liczba lub odwołanie do komórki

Składnia operatora Ampersand:

=Wartość_1 i Wartość_2

Gdzie,

Wartość_1 to wiodące zera do wstawienia przed liczbą, a Wartość_2 to liczba.

Na przykład, aby dodać tylko dwa zera przed liczbą, użyj jednej z tych formuł:

=ZŁĄCZ.TEKSTY("00";A2)

Pierwszy argument to dwa zera („00”), ponieważ chcemy dodać dwa zera przed liczbą w A2 (która jest drugim argumentem).

Lub, 

="00"&A2

Tutaj pierwszym argumentem są 2 zera, po których następuje operator „&”, a drugim argumentem jest liczba.

Jak widać, formuła dodaje tylko dwa wiodące zera do wszystkich liczb w kolumnie, niezależnie od tego, ile cyfr zawiera liczba.

Obie te formuły łączą pewną liczbę zer przed oryginalnymi liczbami i przechowują je jako ciągi tekstowe.

Korzystanie z funkcji REPT/LEN

Jeśli chcesz dodać wiodące zera do danych numerycznych lub alfanumerycznych i przekonwertować ciąg na tekst, użyj funkcji POWT. Funkcja REPT służy do powtarzania znaku (znaków) określoną liczbę razy. Ta funkcja może być również używana do wstawiania stałych liczb wiodących zer przed liczbą.

=POWT(tekst;liczba_razy)

Gdzie „tekst” to znak, który chcemy powtórzyć (w naszym przypadku „0”), a argument „liczba_razy” to liczba powtórzeń tego znaku.

Na przykład, aby wygenerować pięć zer przed liczbami, formuła wyglądałaby tak:

=POWT(0,5)&A2

Formuła powtarza 5 zer i łączy ciąg liczb w A2 i zwraca wynik. Następnie formuła jest stosowana do komórki B2:B6 za pomocą uchwytu wypełniania.

Powyższy wzór dodaje stałą liczbę zer przed liczbą, ale całkowita długość liczby zmienia się w zależności od liczby.

Jeśli chcesz dodać wiodące zera wszędzie tam, gdzie jest to potrzebne, aby utworzyć ciągi o określonej długości (o stałej długości), możesz użyć funkcji REPT i LEN razem.

Składnia:

= POWT (tekst; liczba_razy-LEN(tekst))&komórka

Na przykład, aby dodać zera poprzedzone prefiksem do wartości w komórce A2 i utworzyć łańcuch o długości 5 znaków, wypróbuj następującą formułę:

=POWT(0,5-DŁ(A2))&A2

Tutaj „LEN(A2)” pobiera całkowitą długość ciągu/liczb w komórce A2. „5” to maksymalna długość ciągu/liczb, jakie powinna mieć komórka. A część „REPT(0,5-LEN(A2))” dodaje liczbę zer, odejmując długość ciągu w A2 od maksymalnej liczby zer (5). Następnie liczba zer jest łączona przed wartością A2, tworząc ciąg o stałej długości.

Korzystanie z PRAWEJ funkcji

Innym sposobem dopełnienia wiodących zer przed ciągiem w programie Excel jest użycie funkcji PRAWO.

Funkcja PRAWY może dodać liczbę zer na początku liczby i wyodrębnić N znaków z prawej strony z wartości.

Składnia:

= PRAWO (tekst, liczba_znaków)
  • tekst to komórka lub wartość, z której chcesz wyodrębnić znaki.
  • liczba_znaków to liczba znaków do wyodrębnienia z tekstu. Jeśli ten argument nie zostanie podany, wyodrębniony zostanie tylko pierwszy znak.

W przypadku tej metody łączymy maksymalną liczbę zer z odwołaniem do komórki, która zawiera ciąg w argumencie „tekst”.

Aby utworzyć 6-cyfrową liczbę na podstawie ciągu liczbowego w A z wiodącymi zerami, wypróbuj następującą formułę:

=PRAWY("0000000"&A2,6)

Pierwszy argument (tekst) formuły dodaje 7 zer do wartości w A2 („0000000” i A2), a następnie zwraca 7 znaków z prawej strony, co daje kilka zer wiodących.

Dodawanie wiodących zer za pomocą niestandardowego formatowania liczb

Jeśli użyjesz którejkolwiek z powyższych metod, aby umieścić wiodące zera przed liczbami, zawsze otrzymasz ciąg tekstowy, a nie liczbę. I nie będą zbytnio przydatne w obliczeniach lub formułach numerycznych.

Najlepszym sposobem dodawania wiodących zer w programie Excel jest zastosowanie niestandardowego formatowania liczb. Jeśli dodasz wiodące zera, dodając do komórki niestandardowy format liczb, nie zmieni to wartości komórki, a jedynie sposób jej wyświetlania. Wartość nadal będzie liczbą, a nie tekstem.

Aby zmienić formatowanie liczb w komórkach, wykonaj następujące kroki:

Zaznacz komórkę lub zakres komórek, w których chcesz wyświetlić wiodące zera. Następnie kliknij prawym przyciskiem myszy w dowolnym miejscu w wybranym zakresie i wybierz opcję „Formatuj komórki” z menu kontekstowego. Lub naciśnij klawisze skrótu Ctrl + 1.

W oknie Formatowanie komórek przejdź do zakładki „Liczba” i wybierz „Niestandardowe” w opcjach kategorii.

Wprowadź liczbę zer w polu „Typ:”, aby określić całkowitą liczbę cyfr, które chcesz wyświetlić w komórce. Na przykład, jeśli chcesz, aby numer miał 6 cyfr, wprowadź „000000” jako kod formatu niestandardowego. Następnie kliknij „OK”, aby złożyć wniosek.

Spowoduje to wyświetlenie wiodących zer przed liczbami, a jeśli liczba ma mniej niż 6 cyfr, zostanie dopełnione zerem przed nim.

Wydaje się, że liczby mają wiodące zera, podczas gdy wartość bazowa pozostanie niezmieniona. Jeśli wybierzesz komórkę z niestandardowym formatowaniem, wyświetli się oryginalna liczba na pasku formuły

Istnieje wiele cyfrowych symboli zastępczych, których możesz użyć w niestandardowym formacie liczb. Ale są tylko dwa podstawowe symbole zastępcze, których można użyć do dodawania wiodących zer w liczbach.

  • 0 – Jest to symbol zastępczy cyfr, który wyświetla dodatkowe zera. Wyświetla wymuszone cyfry od 0 do 9, niezależnie od tego, czy cyfra ma związek z wartością. Na przykład, jeśli wpiszesz 2.5 z kodem formatu 000.00, wyświetli się 002.50.
  • # – Jest to symbol zastępczy cyfr, który wyświetla opcjonalne cyfry i nie zawiera dodatkowych zer. Na przykład, jeśli wpiszesz 123 z kodem formatowania 000#, wyświetli się 0123.

Ponadto wszelkie znaki interpunkcyjne lub inne znaki zawarte w kodzie formatu będą wyświetlane bez zmian. Możesz używać znaków takich jak myślnik (-), przecinek (,), ukośnik (/) itp.

Na przykład możesz również sformatować numery jako numery telefonów przy użyciu formatu niestandardowego.

Kod formatu w oknie dialogowym Formatowanie komórek:

Wynik:

Zastosujmy ten kod formatujący w następującym przykładzie:

##0000

Jak widać, „0” dodaje dodatkowe zera, a „#” nie dodaje nieznacznych zer:

Możesz również użyć wstępnie zdefiniowanych kodów formatu w sekcji „Formaty specjalne” okna dialogowego Formatuj komórki dla kodów pocztowych, numerów telefonów i numerów ubezpieczenia społecznego.

W poniższej tabeli przedstawiono liczby z wiodącymi zerami, w których różne kody formatu „Specjalne” są stosowane do różnych kolumn:

Usuwanie wiodących zer w programie Excel

Teraz nauczyłeś się dodawać wiodące zera w Excelu, zobaczmy, jak usunąć wiodące zera z liczby ciągów. Czasami podczas importowania danych z zewnętrznego źródła liczby mogą mieć przedrostek zer i być sformatowane jako tekst. W takich przypadkach musisz usunąć wiodące zera i przekonwertować je z powrotem na liczby, aby móc używać ich w formułach.

Istnieje wiele sposobów usuwania zer wiodących w programie Excel, a my zobaczymy je jeden po drugim.

Usuń wiodące zera, zmieniając formatowanie komórek

Jeśli wiodące zera zostały dodane przez niestandardowe formatowanie liczb, możesz je łatwo usunąć, zmieniając format komórek. Możesz stwierdzić, czy Twoje komórki są sformatowane w sposób niestandardowy, patrząc na pasek adresu (zera będą widoczne w komórce, a nie na pasku adresu).

Aby usunąć zera z przedrostkiem, wybierz komórki z wiodącymi zerami, kliknij pole „Format liczb” i wybierz opcję formatowania „Ogólne” lub „Liczba”.

Teraz zniknęły wiodące zera:

Usuń wiodące zera, konwertując tekst na liczby

Jeśli wiodące zera zostały dodane przez zmianę formatu komórki lub dodanie apostrofów przed liczbami lub automatycznie dodane podczas importowania danych, najłatwiejszym sposobem przekonwertowania ich na liczby jest użycie opcji sprawdzania błędów. Oto jak to robisz:

Możesz użyć tej metody, jeśli liczby są wyrównane do lewej, a komórki mają mały zielony trójkąt (wskaźnik błędu) w lewym górnym rogu komórek. Oznacza to, że liczby są sformatowane jako tekst.

Wybierz te komórki, a zobaczysz żółte ostrzeżenie w prawej górnej części zaznaczenia. Następnie kliknij opcję „Konwertuj na numer” z menu rozwijanego.

Twoje zera zostaną usunięte, a liczby zostaną przekonwertowane z powrotem do formatu liczbowego (wyrównane do prawej).

Usuwanie wiodących zer przez Mnożenie/dzielenie przez 1

Innym łatwym i najlepszym sposobem na usunięcie wiodącej wartości jest pomnożenie lub podzielenie liczb przez 1. Dzielenie lub pomnożenie wartości nie zmienia wartości, po prostu konwertuje wartość z powrotem na liczbę i usuwa wiodące zera.

Aby to zrobić, wpisz w komórce formułę z poniższego przykładu i naciśnij klawisz ENTER. Wiodące zera zostaną usunięte, a ciąg zostanie ponownie przekonwertowany na liczbę.

Następnie zastosuj tę formułę do innych komórek za pomocą uchwytu wypełniania.

Możesz osiągnąć te same wyniki, używając polecenia „Wklej specjalnie”. Oto jak:

Wpisz w komórce wartość liczbową „1” (powiedzmy w B2) i skopiuj tę wartość.

Następnie wybierz komórki, w których chcesz usunąć wiodące zera. Następnie kliknij prawym przyciskiem myszy zaznaczenie, a następnie wybierz opcję „Wklej specjalnie”.

W oknie dialogowym Wklej specjalnie, w obszarze Operacja, wybierz opcję „Pomnóż” lub „Podziel” i kliknij „OK”.

To wszystko, twoje wiodące zera zostaną usunięte, pozostawiając ciągi jako liczby.

Usuń wiodące zera za pomocą formuł

Innym łatwym sposobem na usunięcie zer z przedrostkiem jest użycie funkcji WARTOŚĆ. Ta metoda może być przydatna, niezależnie od tego, czy wiodące zera zostały dodane przy użyciu innej formuły, apostrofu lub niestandardowego formatowania.

=WARTOŚĆ(A1)

Argumentem formuły może być wartość lub odwołanie do komórki, która ma tę wartość. Formuła usuwa wiodące zera i konwertuje wartość z tekstu na liczbę. Następnie nałóż formułę na pozostałe komórki.

Czasami możesz chcieć usunąć wiodące zera, ale chcesz zachować liczby w formacie tekstowym. W takich przypadkach musisz użyć funkcji TEKST() i WARTOŚĆ() razem w następujący sposób:

=TEKST(WARTOŚĆ(A1);"#")

Funkcja WARTOŚĆ konwertuje wartość w A1 na liczbę. Ale drugi argument, „#” konwertuje wartość z powrotem do formatu tekstowego bez żadnych dodatkowych zer. W rezultacie otrzymasz liczby bez zer wiodących, ale nadal w formacie tekstowym (wyrównanym do lewej).

Usuń wiodące zera za pomocą funkcji tekstu do kolumn w programie Excel

Jeszcze innym sposobem na usunięcie wiodących zer jest użycie funkcji Tekst do kolumn programu Excel.

Wybierz zakres komórek, które mają liczby z wiodącymi zerami.

Następnie przejdź do zakładki „Dane” i kliknij przycisk „Tekst do kolumn” w grupie Narzędzia danych.

Pojawi się kreator „Konwertuj tekst na kolumny”. W kroku 1 z 3 wybierz „Rozdzielone” i kliknij „Dalej”.

W kroku 2 z 3 usuń zaznaczenie wszystkich ograniczników i kliknij „Dalej”.

W ostatnim kroku pozostaw opcję Format danych kolumny jako „Ogólne” i wybierz miejsce docelowe (pierwsza komórka zakresu), w którym chcesz umieścić liczby bez wiodących zer. Następnie kliknij „Zakończ”

I otrzymasz liczby z usuniętym wiodącym w osobnej kolumnie, jak pokazano poniżej.

To wszystko.