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

xpil - 2018/07/26 - Branżowe /

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?

11
Dodaj komentarz

avatar
6 Comment threads
5 Thread replies
4 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
xpilSpeXRzast40i6 Recent comment authors
  Subscribe  
najnowszy najstarszy oceniany
Powiadom o
40i6
Gość

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ą.

Rzast
Gość
Rzast

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 😉

SpeX
Gość

A nie wystarczy skorzystać z formuły =wartość(tekst)

SpeX
Gość

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ę”

40i6
Gość

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.

40i6
Gość

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…

%d bloggers like this: