W Excelu często przetwarzamy dane pochodzące z różnych systemów informatycznych. Nierzadko musimy poradzić sobie z dziwnymi znakami. Jednym z nich jest tzw. twarda spacja. Jak usunąć ten znak? O tym przeczytasz w tym artykule.
Usunięcie przez Ctrl+H
Twarda spacja to znak, który wygląda jak zwykła spacja, ale nim nie jest.
Wywołanie skrótu Ctrl+H i próba zamiany znaku zwykłej spacji na pusty ciąg, nie daje żadnego efektu, kod tego znaku to bowiem 160, zaś kod zwykłej spacji to 32.
Konieczne jest skopiowanie znaku twardej spacji i wklejenie go do pierwszego pola na karcie Zamień okienka Znajdowanie i zamienianie. Wtedy wszystko zadziała.
Kasacja makrem
Twarda spacja w arkuszu jest znakiem niepożądanym i przeszkadza w wyliczeniach, czego dowodem może być poniższy przykład.
Dane źródłowe
Screen pierwszy pokazuje dane pierwotne. Widzimy, że niektóre kwoty, po zaczytaniu do Excela, zostały błędnie rozpoznane jako tekst. Świadczy o tym wyrównanie do lewej strony komórki.
Kod VBA
Użycie makra pozwala nam na konwersję tych kwot wyglądających jak liczby do postaci prawdziwych liczb.
|
1 2 3 4 5 6 7 8 9 10 11 |
Private Const msMODUL As String = "MMakroGlowne" Option Explicit Public Sub MakroGlowne() 'Wywołaj funkcję dla arkusza wksDane KasujTwardeSpacje wksDane.UsedRange End Sub |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
Public Sub KasujTwardeSpacje(ByRef rngObszar As Range) Dim rngCelka As Range 'Pojedyncza komórka Dim sWartoscCelki As String 'Wartość komórki (pobrana) Dim bCzyTwardaSpacja As Boolean 'Sprawdza czy jest twarda spacja w wartości Dim sWartoscBezSpacji As String 'Wartość komórki (po usunięciu spacji) Dim dNowaWartosc As Double 'Nowa wartość Dim lLicznik As Long 'Zliczamy komórki z twardymi spacjami Const sPROC As String = "KasujTwardeSpacje" Const sFORMAT_KS As String = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" 'Aktywuj obsługę błędów 1 On Error GoTo ObslugaBledu 'Pętla po wszystkich komórkach w zakresie 2 For Each rngCelka In rngObszar.Cells 'Zaczytaj bieżącą wartość komórki 3 sWartoscCelki = rngCelka.Value 'Bierz pod uwagę tylko niepuste komórki 4 If Len(sWartoscCelki) <> 0 Then 'Sprawdź czy w komórce znajduje się twarda spacja 5 bCzyTwardaSpacja = InStr(1, sWartoscCelki, Chr(160)) > 0 'Działaj tylko na komórkach, które zawierają twardą spację 6 If bCzyTwardaSpacja Then 'Zwiększ licznik 7 lLicznik = lLicznik + 1 'Usuń twardą spację 8 sWartoscBezSpacji = Trim(WorksheetFunction.Substitute( _ sWartoscCelki, Chr(160), "")) 'Sprawdzamy czy wartość bez spacji jest liczbą 9 If IsNumeric(sWartoscBezSpacji) Then 10 dNowaWartosc = CDbl(sWartoscBezSpacji) 11 rngCelka.Value = dNowaWartosc 12 rngCelka.NumberFormat = sFORMAT_KS 13 End If '// If IsNumeric(sWartoscBezSpacji) Then 14 End If '// If bCzyTwardaSpacja Then 15 End If '// If Len(sWartoscCelki) <> 0 Then 16 Next rngCelka '// For Each rngCelka In rngObszar.Cells 'Wyświetl info 17 MsgBox "Operacja zakończona." & vbCr & _ "Liczba komórek, w których usunięto twarde spacje: " & lLicznik Wyjscie: 18 Set rngCelka = Nothing 19 On Error GoTo 0 20 Exit Sub ObslugaBledu: 21 Application.ScreenUpdating = True 22 MsgBox "Wystąpił błąd nr " & Err.Number & " (" & Err.Description & ")." & _ vbCr & vbCr & "Linia kodu nr " & Erl & " w procedurze " & _ "'" & sPROC & "' modułu '" & msMODUL & "'.", vbInformation, "BŁĄD!" 23 Resume Wyjscie End Sub |
Dane przetworzone
Usunięcie twardych spacji pozwoliło właściwie przekonwertować tekst na liczby.
Komentarz
Podane makro może wydawać się nieco nadmiarowe ponieważ tak naprawdę wystarczy skorzystać z metody Replace obiektu Range.
W takiej sytuacji obszarem może być zakres roboczy UsedRange lub wszystkie komórki arkusza. W parametrze What wpisujemy Chr(160) zaś w Replacement podajemy pusty ciąg vbNullString.
Makro skasuje nam twarde spacje z komórek jednak nie wyrówna otrzymanych w wyniku liczb do prawej strony. Estetom polecam więc uniwersalne makro zawarte w tej notce, natomiast warto pamiętać, że istnieje rozwiązanie prostsze i szybsze, nie bazujące na pętli.
