Zamiana wartości tekstowej na liczbę w Excel

Funkcje tekstowe w Excelu zawsze zwracają ciąg znaków przechowywany jako tekst. Nawet gdy ciąg ten będzie wyglądał jak liczba, funkcja zwróci go w postaci tekstu. Taki efekt jest często niepożądany, gdyż użytkownik wyciągający liczby ma zamiar ich dalszej obróbki pod postacią liczbową np. analizowania z wykorzystaniem tabel przestawnych. Warto wspomnieć, że wewnątrz komórki, wartości tekstowe są wyrównywane do lewej strony, liczbowe do prawej. Umożliwia to łatwą identyfikację typu danych.  Zamiana wartości tekstowej na liczbę w Excel możliwa jest na 8 znanych mi sposobów, którymi będą:

1. Podwójny minus „–”
2. Wklej specjalnie, dodaj 0
3. Operacja matematyczna na funkcji tekstowej dodająca 0
4. Tekst jako kolumny
5. Funkcja WARTOŚĆ
6. Konwertuj na liczbę – zielony trójkącik
7. Power Query
8. VBA

1. Podwójny minus „–”

Pierwsza metoda będzie polegała za zastosowaniu w formule tekstowej FRAGMENT.TEKSTU podwójnego znaku „-”. Pierwszy „-” zmienia wartość tekstową na liczbę ujemną, a drugi zmienia wartość ujemną na dodatnią. Jest to najbardziej efektywna metoda gdy chcemy zamienić wartość na liczbę wewnątrz formuły. Sprawdza się szczególnie gdy naszą funkcję przeciągamy na wiele tysięcy wierszy w dół.

=--FRAGMENT.TEKSTU(A2;5;3)

2. Wklej specjalnie, dodaj 0

Druga opcja polega na wykonaniu operacji matematycznej na ciągu tekstowym. W efekcie czego tekst zostaje automatycznie zamieniony na wartość liczbową. Polecam tą metodę gdy chcemy przekonwertować wiele komórek, ale wartości w nich przechowywane nie są wynikami formuł. Metoda działa bardzo sprawnie, nawet operując na wielu tysiącach wierszy.

3. Operacja matematyczna na funkcji tekstowej dodająca 0

Trzecia metoda to w pewnym sensie połączenie metody pierwszej i drugiej. Zamiast stosowania podwójnego znaku „-” wykonujemy na formule operację matematyczną. W tym przypadku będzie to dodanie „0”. Równie dobrze możemy zastosować inną operację na liczbach, np. mnożenie przez 1, odjęcie 0. Ważne, aby operacja ta nie zmieniła naszej wartości.

=FRAGMENT.TEKSTU(A2;5;3)+0

4. Tekst jako kolumny

Na pewno zdarzyło się Wam używać kreatora konwersji „Tekst jako kolumny” dostępnego na karcie „Dane”. Zazwyczaj używamy go do podzielenia wartości przechowywanych w jednej kolumnie (podzielonych ogranicznikami np. przecinkami, średnikami, pauzami, itp.), na wiele kolumn. A czy wiedzieliście, że za pomocą tego kreatora możemy kilkoma kliknięciami zamienić tekst na liczbę?

5. Funkcja WARTOŚĆ

Piąta opcja to konwersja tekstu na liczbę za pomocą formuły WARTOŚĆ. Funkcja ta przyjmuje tylko jeden argument. Efektem jej działania jest konwersja ciągu tekstowego reprezentującego liczbę na „prawdziwą” liczbę.

=WARTOŚĆ(B2)

6. Konwertuj na liczbę – zielony trójkącik

Użytkownicy Excela korzystają z tej metody prawdopodobnie najczęściej. Wg domyślnych ustawień programu Excel komórki zawierające błędy oznaczane są zielonymi trójkątami. Są one widoczne w lewych górnych rogach dla każdej takiej komórki. Jako błędy interpretowane są także liczby przechowywane w postaci tekstowej.

Wybierając komórkę z trójkącikiem wyświetla się ikona żółtego wykrzyknika. Po kliknięci w nią rozwija się menu informujące, że o rodzaju błędu z jakim mamy do czynienia. W przypadku chęci zamiany wartości wybieramy opcję „Konwertuj na liczbę”.

Zdecydowanie nie polecam tego sposobu. Jest to mało wydajna metoda, co jest szczególnie zauważalne gdy chcemy przekonwertować większą ilość wierszy – potrafi zawiesić Excela nawet na kilka minut. Domyślne ustawienie wyświetlania zielonych trójkącików można zmienić wchodząc w Plik > Opcje > Formuły > Sprawdzanie błędów.

7. Power Query

Przedostatnia metoda będzie polegała na stworzeniu zapytania w Power Query. Sposób jest o tyle ciekawy, że takie zapytanie przygotowujemy raz, później wystarczy tylko je odświeżyć. Wszystkie dodane wiersze będą automatycznie uwzględniane przez zapytanie, konwertowane i zwracane w tabeli wynikowej.

Kod M dla wykorzystanego zapytania:

let
Źródło = Excel.CurrentWorkbook(){[Name="Tabela7"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Kod", type text}}),
#"Zduplikowano kolumnę" = Table.DuplicateColumn(#"Zmieniono typ", "Kod", "Kod — kopia"),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Zduplikowano kolumnę",{{"Kod — kopia", "Numer jako tekst"}}),
#"Wyodrębniony zakres tekstu" = Table.TransformColumns(#"Zmieniono nazwy kolumn", {{"Numer jako tekst", each Text.Middle(_, 4, 3), type text}}),
#"Zduplikowano kolumnę1" = Table.DuplicateColumn(#"Wyodrębniony zakres tekstu", "Numer jako tekst", "Numer jako tekst — kopia"),
#"Zmieniono nazwy kolumn1" = Table.RenameColumns(#"Zduplikowano kolumnę1",{{"Numer jako tekst — kopia", "Numer jako liczba"}}),
#"Zmieniono typ1" = Table.TransformColumnTypes(#"Zmieniono nazwy kolumn1",{{"Numer jako liczba", Int64.Type}})
in
#"Zmieniono typ1"

Więcej wpisów o Power Query

8. VBA

Ostatnia, ósma metoda, która chcę zaprezentować, to procedura zapisana w języku Visual Basic for Applications. Użycie funkcji VBA o nazwie CInt spowoduje zamianę tekstu przechowywanego w każdej komórce w obrębie aktywnego zaznaczenia na liczbę całkowitą (Integer). Może się okazać pomocna jako element jakiejś bardziej skomplikowanej procedury lub funkcji.

Kod procedury w języku VBA:

Sub NaLiczbę()
   For Each Cell In Selection
      Cell.Value = CInt(Cell.Value)
   Next Cell
End Sub

Więcej wpisów o VBA

Podsumowanie

Zamiana wartości tekstowej na liczbę w Excel jest możliwa do przeprowadzenia za pomocą wielu metod. W zależności od potrzeby i sytuacji możemy stosować je przemiennie. Osobiście nie polecam najbardziej popularnej metody – konwersji na liczbę komórek oznaczanych zielonym trójkącikiem. Można ją zastąpić wieloma bardziej wydajnymi sposobami.

Jaki jest Twój ulubiony sposób na zamianę tekstu na liczbę w Excel? Może znasz jakiś inny, nieopisany we wpisie? Podziel się swoimi spostrzeżeniami w komentarzu 🙂