VBA funkcie INSTR a REPLACE

 

Určite mi dáte za pravdu, že pri práci s datami je hľadanie znaku v bunke a nahradzovanie písmena, poprípade výrazu jednou z rutín, ktoré každý analytik ovláda.

Považujme riešenie pomocou štandarných excelovských funkcií za samozrejmosť a ukážeme si, ako hľadať a nahradzovať znaky pomocou procedúry VBA. Ak sa zameráme na ukážku vo forme nasledujúceho obrázku, zistíme, že niektorré výrazy obsahujú anomáliu v tvare %%.

Našou úlohou bude:

1) Nájsť vrámci stĺpca v každej bunke túto anomáliu: %%

2) Ak sa daná anomália v bunke nájde, tak Excel vymení “-“ za “_”

Pre nájdenie konkrétneho znaku (výrazu) použijeme VBA funkciu INSTR:

INSTR( štart ; text ; hľadaný znak alebo výraz )

Kde atribút štart je počiatočné písmenko, od ktorého má funckia prehľadávať výraz. Tento atribút nie je povinný. Ak ho teda nepoužijeme, funkcia začne automaticky prehľadávať od prvého písmenka. Funckia obsahuje ešte jeden nepovinný atribút, ktorým je spôsob porovnávania. Používa sa vbBinaryCompare (rozlišuje malé a veľké písmenka) a vbTextCompare (nerozlišuje malé a veľké písmenka).

V pripravenom template bude použitá hodnota referenčnej bunky Cells(i, 1).Value, kde finálny výraz bude vyzerať nasledujúco:

InStr(Cells(i, 1).Value, "%%")

Pre nahradenie spomenutých čiarok použijeme funkciu REPLACE:

Replace( text ; hľadaný text ; nový text )

Ako to bolo v predchádzajúcej funckii, aj REPLACE má o niečo viac atribútov, ktoré sú nepovinné. Ja spomeniem štart, ktorý sa zadáva hneď po atribúte nový text.a pomocou ktorého v konečnom dôsledku definujeme, od ktorého písmenka, vrámci textu má funkcia nahradzovať. Aplikácia v našom príklade mať nasledujúcu podobu:

Replace(Cells(i, 1), "-", "_")

Nakoniec stačí pridať zopár premenných, FOR cyklus a podmienku IF. Výsledný kód vyzerá nasledovne:

Sub NajstNahradit()

Dim i As Integer
Dim UB As Integer

    UB = Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = 2 To 10
        If InStr(Cells(i, 1).Value, "%%") > 0 Then
            Cells(i, 1) = Replace(Cells(i, 1), "-", "_")
        End If
    Next i

End Sub

Celá procedúra spolu s príkladom je k dispozícii v nasledujúcom template. Stačí kliknúť na obrázok Download. V prípade, dotazov sa neváhajte opýtať na Facebooku (Klikni na logo Excel-u vedľa Download), stačí dať like a napísať správu.