Prosty, ale mało znany trick Excel do masowej zamiany tekstu na liczby

https://xpil.eu/z6o

Każdy, kto choć przez chwilę używał Excela prędzej czy później trafi na następujący problem:

  1. Mam mnóstwo liczb w formacie tekstowym ("popsute")
  2. Chcę je "naprawić" tj. zamienić tekst na liczby

Jak to ugryźć?

Metoda #1: mróweczka

Jeżeli wywalić słowo "mnóstwo" z punktu pierwszego, wówczas wystarczy:

  1. Zaznaczyć wszystkie interesujące nas komórki
  2. Usunąć formatowanie (w angielskiej wersji Excela: Alt-E-A-F)
  3. Przejść do pierwszej komórki w zakresie
  4. Nacisnąć F2 - Enter
  5. Przejść do kolejnej komórki
  6. Nacisnąć F2 - Enter
  7. Powtarzać podpunkty 5 i 6 aż "naprawimy" wszystkie komórki

Powyższy sposób, choć prosty, ma jednak tę wadę, że nadaje się do zakresów pięcio-, góra piętnastokomórkowych. Przy większych zakresach danych przydałaby się coś bardziej uniwersalnego.

Metoda #2: mnożymy kolumnę przez 1

Jeżeli nasze "popsute" liczby siedzą w pojedynczej kolumnie, wówczas:

  1. Wstawiamy pustą kolumnę obok (na przykład po prawej stronie)
  2. Wpisujemy w tej pustej kolumnie formułę "=A2*1" (zamiast A2 wpisujemy adres pierwszej komórki na samej górze naszej kolumny)
  3. Kopiujemy formułę w dół do końca danych
  4. Kopiujemy całą kolumnę z nową formułą
  5. Wklejamy spcjalnie (tylko wartości) do oryginalnej kolumny
  6. Usuwamy kolumnę dodaną w punkcie 1

Proste, szybkie, wygodne. Jedna wada - nie zadziała dla wielu kolumn. Albo inaczej: zadziała, ale będziemy potrzebowali wstawić obok tyle pustych kolumn, ile mamy kolumn z "popsutymi" liczbami. Przy większej ilości kolumn trzeba uważać, no i trwa to odrobinę dłużej.

Metoda #3: mnożymy wszystko przez 1, bez dodatkowych kolumn

Trzecia metoda jest najbardziej uniwersalna, a jednocześnie najmniej znana. Wstyd przyznać, sam poznałem ją stosunkowo niedawno:

  1. W dowolnej pustej komórce arkusza wpisujemy jedynkę.
  2. Zaznaczamy komórkę z tą jedynką i kopiujemy do schowka (Ctrl-C)
  3. Zaznaczamy wszystkie "popsute" komórki
  4. Wklej specjalnie, zaznaczamy "Multiply" (w polskiej wersji to pewnie będzie "Przemnóż"), pukamy Enter.

Co się właśnie wydarzyło?

Przemnożyliśmy wszystkie komórki docelowe przez 1, a jednocześnie nie musieliśmy tworzyć żadnych dodatkowych kolumn.

Proste?

https://xpil.eu/z6o

11 komentarzy

  1. Ja mam często problem z danymi przeniesionymi do OpenOffice Calc z innego programu. Często data, np. 2018-07-26 ma z przodu jak gdyby apostrof, którego nie da się usunąć poprzez skopiowanie go i akcję znajdź i zamień. Zabawne, ale wtedy zbiorowa zamiana np. 2 na 2 pomaga i data staje się datą.

  2. Czyli tak samo, jak w JS najprostrza zmiana wartości tekstowej na liczbową var liczba=1*tekst – zwłaszcza przy pytaniu przez formularze o jakieś wartości, które docelowo mają być liczbą…
    To je informatyka, tego nie zrozumiesz 😉

    1. Oczywiście, ale to wymaga użycia tylu komórek z formułą, ile mamy “popsutych” wartości, analogicznie do tego, co opisuję w metodzie #2. A metoda #3 wymaga tylko jednej dodatkowej komórki z jedynką – bardziej oszczędnie się nie da 😉

  3. ps. Popracował byś nad konfiguracją subskrypcji komentarzy, bo coś nie tak do końca.

    Np. mail o nowym komentarzu:
    “Pojawiła się nowa odpowiedź, byłeś nią zainteresowany w

    Anuluj subskrypcję”

  4. Brakuje mi w arkuszach kalkulacyjnych pewnego drobiazgu i ten brak często wkurza. Może za dużo bym chciał, ale gdy wklejam dane z kolumną zawierającą nr PESEL i zapominam wcześniej uprzedzić Excel/OO, że to będzie pole tekstowe, to zastanawiam się, czy tylko mi to przeszkadza?
    Programista być może mógłby jakoś ogarnąć tę sytuację. Jeżeli w pole bez formatu wklejam ciąg znaków o długości >1 złożony tylko z cyfr i jeżeli zaczyna się on zerem, to cała kolumna powinna być tekstowa. Zaraz jednak myślę, że nie znam 100 powodów (które zna programista), dla których nie byłoby to działanie słuszne.

    1. No więc tak: zaznaczasz wszystkie komórki (Ctrl-A), otwierasz menu formatowania komórek (Ctrl-jedynka), klikasz “Tekst” i potem “OK”. Przy odrobinie wprawy całość zajmie Ci półtora sekundy.

      Alternatywa: piszesz sobie kawałek kodu VBA, który przy wklejaniu formatuje każdą komórkę liczbową zaczynającą się od zera jako tekst, wrzucasz go do AddIns, aktywujesz i gotowe.
      (przepis tutaj: http://www.ozgrid.com/VBA/excel-add-in-create.htm

      Pewnie jest jeszcze pierdylion innych sposobów.

  5. Czy znasz jakiś sposób aby filtrować pola wg ich formatu? Najbardziej idiotyczne, co może zrobić życzliwy użytkownik, a najczęściej to będzie (dlaczego?!?!?!) ktoś wyżej w hierarchii dziobania, więc nie możesz mu powiedzieć nawet, że jest $%&^%*^&#$%#%& i powinien @#$!@&*%$, to na liście kilku tysięcy rekordów w excelu zaznaczyć niektóre pola na żółto – zaznaczyłem ci interesujące mnie pola, zrób mi z nich zestawienie.
    Czyż można o większe szczęście?
    W Open Office, bo zwyczajowo piszę excel mając na myśli arkusz, mogę zarejestrować makro w JS, albo OO Basic, Python albo BeanShell, ale w żadnym z tych języków nie wybrnę z kłopotu, bo kiedyś VB, czasem PascalScript, ale bardzo skromnie…

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.