TEKST w Excelu: formaty liczb, dat i wynik tekstowy

TEKST tworzy tekstową reprezentację liczby według wskazanego kodu formatu. Angielska nazwa funkcji to TEXT. Przydaje mi się do budowania komunikatu z datą lub wartością, który ma wyglądać w określony sposób. Do dalszych obliczeń zostawiam jednak oryginalną liczbę w osobnej komórce.

Jeśli potrzebujesz tylko wyświetlić dwa miejsca po przecinku, rozważ zwykły format komórki. Zachowuje wartość liczbową. TEKST zmienia rodzaj wyniku na napis, nawet gdy na ekranie nadal widać same cyfry.

Składnia i założenia przykładów

=TEKST(wartość;format_tekst)

Pierwszy argument jest wartością liczbową albo odwołaniem do niej. Drugi to kod formatu zapisany w cudzysłowie. Według dokumentacji Microsoft funkcja obejmuje m.in. Microsoft 365, Excel 2016, 2019, 2021 i 2024.

Przykłady są syntetyczne i zakładają polskie ustawienia z przecinkiem dziesiętnym, spacją tysięcy i średnikiem między argumentami. Przy innym środowisku sprawdź lokalny kod formatu. Samo przetłumaczenie nazwy funkcji nie gwarantuje poprawnego przeniesienia drugiego argumentu.

Liczby: miejsca dziesiętne, procenty i zera wiodące

Zero w kodzie wymusza pokazanie cyfry na danej pozycji. Symbol # pozwala pominąć niepotrzebną cyfrę. Dla przykładu 42 format pięciu zer dopisuje trzy zera z przodu. Nie odzyskuje jednak informacji, której nie było w źródle: długość identyfikatora musisz znać z reguł danych.

Jeżeli identyfikator ma zera wiodące od początku, najlepiej zachować go jako tekst już przy imporcie. Dodanie ustalonej liczby zer na końcu procesu jest poprawne tylko wtedy, gdy znasz oczekiwany format. Przy długich identyfikatorach nie odtworzy też cyfr utraconych wcześniej przez zapis liczbowy.

Daty i godziny: polskie kody mają znaczenie

Załóżmy, że A2 zawiera prawdziwą datę 10 czerwca 2024, a A3 prawdziwy czas 10:45:30. Nie są to niejednoznaczne napisy wymagające dopiero rozpoznania daty.

=TEKST(A2;"dd.mm.rrrr")
=TEKST(A3;"gg:mm")
=TEKST(A3;"gg:mm:ss")

Oczekiwane teksty to 10.06.2024, 10:45 oraz 10:45:30. Poprzednia wersja używała angielskich kodów YYYY i HH w polskich przykładach. Zastąpiłem je kodami roku rrrr i godziny gg, zgodnie z polskimi zasadami formatowania.

Litera m ma znaczenie zależne od otoczenia. W formacie daty opisuje miesiąc, a bezpośrednio po godzinach lub przed sekundami opisuje minuty. Dlatego kod gg:mm jest pełną regułą czasu, a pojedyncze mm nie jest uniwersalnym sposobem wyciągnięcia minut.

W angielskich ustawieniach spotkasz dd.mm.yyyy oraz hh:mm. Przy współdzieleniu pliku nie zakładaj, że tekst kodu automatycznie dostosuje się do każdego języka. Więcej o tych różnicach pokazuję w tłumaczeniu formuł PL/EN.

Jak dobrać kod bez zgadywania

W desktopowym Excelu wybierz komórkę z reprezentatywną wartością, otwórz formatowanie komórek i ustaw oczekiwany wygląd. Następnie zajrzyj do kategorii niestandardowej i pola z kodem formatu. Odpowiednią regułę przenieś do argumentu TEKST, zachowując cudzysłowy.

Taką metodę opisuje Microsoft na stronie funkcji. Pomaga przy regionalnych separatorach i kodach dat. Rozbudowany kod może mieć osobne sekcje dla różnych rodzajów wartości, więc nie kopiuj przypadkowego fragmentu, którego znaczenia nie rozumiesz. Na początek wybierz prosty format odpowiadający jednemu zadaniu.

Jeśli chcesz dopisać zwykłą jednostkę, możesz połączyć wynik z osobnym tekstem. Dla liczby 12,3 w A4 formuła =TEKST(A4;"0,00")&" kg" zwróci 12,30 kg. Unikasz wtedy dodatkowego poziomu cudzysłowów wewnątrz kodu formatu. Apostrof nie jest ogólnym znacznikiem obejmującym tekst dosłowny w kodzie.

TEKST w gotowym komunikacie

Dla daty w A2 użyję ="Data raportu: "&TEKST(A2;"rrrr-mm-dd"). Oczekiwany napis to Data raportu: 2024-06-10. Zwykłe połączenie etykiety z datą bez jawnego formatu może pokazać jej reprezentację liczbową zamiast oczekiwanego wyglądu.

Oryginalna data nadal zostaje w A2. To na niej opieram sortowanie chronologiczne, filtry okresu i obliczenia różnicy dat. Kolumna z gotowym komunikatem służy do prezentacji. Nie każę następnemu odbiorcy ponownie wydobywać daty z długiego napisu.

Usunąłem też kategoryczne twierdzenie, że na wyniku tekstowym nie da się wykonać żadnej operacji matematycznej. Excel bywa w stanie wymusić konwersję tekstu na liczbę, ale jej powodzenie zależy od zapisu. Stabilniejszy model to odwoływanie się do źródła liczbowego, co zaleca producent.

Granice i częste pomyłki

Formatowanie procentowe nie naprawi pomylonej skali. Ułamek 0,25 daje 25%, ale liczba 25 z tym samym kodem daje 2500%. Przed dopisywaniem symbolu procentu ustalam, jak zapisana jest wartość źródłowa. Dodatkowe zero w kodzie nie zmieni znaczenia mianownika.

Kod formatu nie przelicza walut ani jednostek. Dopisanie kg nie zamieni gramów w kilogramy, a etykieta waluty nie wykona przewalutowania. Najpierw przygotuj poprawną wartość, a dopiero potem nadaj jej opis. Wizualnie przekonujący napis może ukrywać źle zdefiniowane dane.

Microsoft wskazuje również, że TEKST nie nadaje koloru wynikowi za pomocą sekcji koloru kodu formatu. Do kolorów użyj formatowania komórki lub warunkowego. Nie oczekuj, że tekst zawierający określoną kwotę zachowa wszystkie właściwości wizualne komórki źródłowej.

Ćwiczenie: wartość i jej etykieta

Zapisz 42 jako liczbę w A2. W B2 użyj =TEKST(A2;"00000"), a w C2 =A2+1. Oczekujesz tekstu 00042 w B2 i liczby 43 w C2. Obliczenie odwołuje się do źródła, nie do gotowej etykiety.

Następnie zmień A2 na 7. Etykieta powinna przyjąć postać 00007, a wynik obliczenia 8. Na końcu opisz, czy pięć cyfr jest rzeczywistą regułą identyfikatora. Funkcja potrafi nadać zapis, ale nie potwierdzi za Ciebie, czy wybrana reguła odpowiada danym.