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ą:
- Podwójny minus „–”
- Wklej specjalnie, dodaj 0
- Operacja matematyczna na funkcji tekstowej dodająca 0
- Tekst jako kolumny
- Funkcja WARTOŚĆ
- Konwertuj na liczbę – zielony trójkącik
- Power Query
- 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"
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
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 🙂
[…] ma być przez funkcję zwracany. W naszym przypadku będzie to ciąg liczb przechowywany jako tekst (przeczytaj więcej o zamianie liczby na tekst w Excel), czyli dopisujemy „As String”. Kod od razu zamykamy wpisując kilka linijek niżej „End […]
Do punktu 8 proponuję użyć „CDbl” dla możliwości zachowania dziesiętnych wartości.
Kod procedury w języku VBA:
Sub NaLiczbę()
For Each Cell In Selection
Cell.Value = CDbl(Cell.Value)
Next Cell
End Sub
W przykładzie z wpisu wartości były liczbami całkowitymi stąd użycie CInt(Cell.Value).
Ale jak najbardziej, jeśli jest potrzeba zaprezentowania wartości dziesiętnych to można zamiennie zastosować CDbl(Cell.Value) albo CDec(Cell.Value)
już pierwszy sposób mi pomógł, dzięki!
używam power query. W tej samej kolumnie (niech będzie kolumna A) mam wiersze w postaci tekstu (wiersz 1-2) i liczby (wiersz 3-4). Nie chcę żeby power query ujednolicało mi format np do postaci tekstu, bo wtedy wiersze z liczbą nie będą prawidłowo działać. A gdy zamieniam na format liczbowy, wtedy wyskakuje error że nie może zamienić tekstu na liczbę. Jak to zrobić? bardzo proszę o pomoc
Podeślij mi proszę na maila excel@jakubkrupa.pl arkusz, który zobrazuje Twój problem i spróbuje coś doradzić.
Zmiana tekstu 299.00 na liczbę,
Żaden z próbowanych metod nie podziałała
Zaręczam, że te metody działają.
Proszę podesłać na mojego maila arkusz z wycinkiem problematycznych danych to wyjaśnię w czym problem.
Witam,
mam taki przypadek, że w 1 kolumnie mam takie dane:
0000000000156934221
0150642022111005779
jak zmienie format na liczbowy, to dostaje wynik:
156934221
1,50642E+17
jak zrobić kod, żeby formtowane były tylko te liczby, które mają mniej lub równe 11 cyfr, a te ktore więcej nie ?
Spróbuj wewnątrz pętli For Each dodać instrukcję warunkową If, która sprawdzi jaka jest długość ciągu Twoich znaków.
[…] Read More […]
[…] Read More […]
[…] + 여기에 자세히 보기 […]
[…] + 여기를 클릭 […]
[…] Read More […]
[…] + 더 읽기 […]
[…] + 여기에 자세히 보기 […]
[…] + 자세한 내용은 여기를 클릭하십시오 […]
[…] Read More […]