Il Metodo Replace. - dal 04/09/04 pagina vista: volte

Replace in italiano si traduce con "rimpiazza", "sostituisci", ed è abbastanza facile capire cosa possiamo ottenere con Replace. Esistono due diversi modi di utilizzare Replace:

  • la Funzione Replace

  • il Metodo Replace

Della Funzione Replace non ne parliamo in questo articolo ritenendo di più facile assimilazione il Metodo Replace. Chi vuole può comunque consultare la guida in linea, dove sono riportati tutti e due gli argomenti, anche se (opinione mia) la guida è un pò avara in questo caso, di comprensibilità.

Il metodo Replace (che chiamerò d'ora in avanti semplicemente Replace) è facile da capire. Questo metodo si può applicare ad un "oggetto" Range (quindi una cella o un insieme di celle), o all' "oggetto" WorksheetFunction (vedi sull'altro sito, sezione vba, articolo "Funzioni Foglio in VBA"). La sua sintassi prevede parecchi argomenti, molti sono facoltativi, e qui riporterò solo gli argomenti necessari, che poi di fatto sono quelli che soprattutto useremo.

  • sintassi:  espressione.Replace(What, Replacement, ..argomenti facoltativi)

traduzione:

  • espressione - è il Range di celle o la cella al cui contenuto vogliamo applicare il "rimpiazzo"

  • What : = è quello che vorremo sostituire del contenuto della cella

  • Replacement : = è ciò che metteremo in sostituzione di What.

esempio: se vogliamo sostituire tutte le lettere "a" contenute nella parola "matta", posta nella cella A1,  con la lettera "o", dovremo scrivere questa istruzione:

  • Range("A1").Replace What:="a", Replacement:="o"

e nella cella vedremo "motto". La sostituzione, il "Replacement", avviene anche per i numeri, non solo per il testo (stringa), possiamo provare questa istruzione: supponiamo di avere in A1 il numero 0154087095, e di voler sostituire lo zero (zero) con un trattino ( - )

  • Range("A1").Replace What:="0", Replacement:="-"

in A1 avremo -154-87-95 . I più attenti avranno già notato che il formato cella è impostato a "testo", altrimenti (a meno che non avessimo usato l'apice prima del numero) non sarebbe stato possibile scrivere un numero intero che inizi con lo zero. Va comunque specificato che Replace restituisce sempre valori stringa, cioè testo, e quando si modificano numeri si rischia di ottenere risultati non desiderati. E sempre opportuno in questi casi effettuare prove per controllare i risultati, prima di applicare sostituzioni di numeri a fogli interi.

Comunque ciò che l'esempio sopra ci insegna è che la sintassi per definire il valore sostituendo (What) e il sostituente (Replacement) prevede l'inserimento degli stessi tra doppi apici, proprio perchè detti valori vengono visti solo come stringhe cioè testo.
Altro vantaggio di questo metodo e che l'operazione di ricerca e sostituzione avviene nella stessa cella in cui si effettua (espresione), non richiedendo quindi altre celle per la conversione.

Il metodo Replace può alternarsi o addirittura essere più vantaggioso ad altri metodi o funzioni, per le ridotte istruzioni da compilare (ad esempio rispetto al metodo Find (trova)) o con risultati migliori rispetto alla funzione Trim (vedi articolo sull'altro sito "Funzioni stringa") che serve ad eliminare gli spazi vuoti da una stringa contenuta in una cella, ma NON elimina più spazi intermedi tra le parole che formano la stringa stessa. Se abbiamo ad esempio la cella B1 con questa frase:(in verde ho colorato gli spazi vuoti solo per renderli meglio visibili)

  A B C

1

       Ermezio    Giuà  


con questa istruzione usando Trim, otteniamo tutto il testo allineato senza spazi a sinistra, mentre tra le due parole lo spazio è rimasto il solito:    [B1] = Trim([B1])

  A B C

1

   Ermezio    Giuà  

mentre usando Replace come in questo esempio, il risultato sarà questo, cioè tutti gli spazi vuoti eliminati tranne un solo spazio tra le due parole:   [B1].Replace What:=" ", Replacement:=""

  A B C

1

   Ermezio Giuà  

cioè abbiamo usato Replace dicendogli di sostituire lo spazio (" " due doppi apici con uno spazio vuoto in mezzo) con ("" due doppi apici senza spazio in mezzo) . Vedremo poi come eliminare ev. tutti gli spazi vuoti.

Ognuno potrà capire le molteplici possibilità che questo metodo ci consente : gestire la sostituzione di valori con altri, metodo che si presta con facilità ad essere inserito anche in cicli For..Next, oppure in istruzioni condizionali (If..Then..End If), anche in database ampi ed estesi. Questo esempio sotto, sostituirà su tutto il foglio1, in ogni cella occupata, in un colpo solo, le lettere "COD" rimpiazzandole con "ART" :

Worksheets("Foglio1").UsedRange.Replace What:="COD", Replacement:="ART"

Se farete qualche prova, inserite questa istruzione in una macro, ovviamente sostituendo i termini assegnati a What e a Replacement con ciò che vorrete, e vedrete la velocità di esecuzione.

Attenzione a una cosa: il metodo è CaseSensitive, quindi occhio alle maiuscole e minuscole di ciò che andate sostituendo, altrimenti non otterrete risultato, e questo nonostante si possa usare l'argomento MatchCase:= True come opzione da usare nelle istruzioni ( Il valore True di MatchCase dovrebbe consentire di rilevare le maiuscole, ma così non avviene, almeno nelle verifiche che ho fatto).

Sarà invece possibile usare delle variabili da assegnare sia a What sia a Replacement, questo per rendere una maggiore flessibilità nell'uso. Potremo usare delle InputBox per reperire i termini, dichiarando le variabili come String, esempio:

  • Sub Sostituisci()
    Dim X As String
    Dim Y As String
    X = InputBox("scrivi cosa vuoi sostituire")
    Y = InputBox("scrivi che cosa vuoi in sostituzione")
    Worksheets("Foglio1").UsedRange.Replace What:=X, Replacement:=Y, _
    SearchOrder:=xlByColumns, MatchCase:=True
    End Sub

In questa istruzione ho inserito anche SearchOrder con la costante che effettuerà la ricerca per colonne, ed il MatchCase. Unico accorgimento: se non scriviamo niente o annulliamo uscendo dalle InputBox, le variabili saranno assimilate a "vuoto". Quindi, attenzione alla seconda inputbox se non scriviamo niente o la annulliamo, perchè se nella prima inputbox abbiamo scritto qualcosa, quel "qualcosa" verrà sostituito col vuoto, o meglio verrà cancellato. Magari la si può adoperare proprio per questa ipotesi, ma è meglio farlo volontariamente.

Un interessante esercizio basato su Replace, scaturito da un lavoro di Migliore Mauro, Mauro.Migliore@bnlmail.com a cui spetta il merito dell'idea della scomposizione e riassemblaggio che poi ho usato anch'io nell'esempio che vedrete, e di cui ho sviluppato le istruzioni in vba.

L'esercizio si basa su una ipotetica tabella nella quale siano inseriti dei numeri di telefono, numeri però scritti come a volte si faceva quando esistevano i prefissi divisi dai numeri telefonici, e ove spesso usavamo per "staccare" gli uni dagli altri, sia spazi vuoti, sia barre ( / ), o trattini ( - ) o ancora punti ( . ) , o virgole ( , ), dipendeva dalle abitudini di ognuno. Vediamo un esempio impostato nella colonna A:

Come si nota sono stati inserite un pò tutte le situazioni viste sopra, ovviamente con numeri di fantasia, addirittura mescolando i vari segni in modo da esaltare poi i risultati.

Cosa vogliamo ottenere:

  • vogliamo come risultato solo i numeri senza nessun segno "estraneo" o spazi. Così, come vediamo nella colonna B, eliminando quindi tutti i segni che non sono numeri e gli spazi :

Ora, per effettuare il Replace dei vari segni e degli spazi vuoti, eliminandoli, potevamo effettuare il ciclo dei rimpiazzi, sfruttando gli stessi valori, e nella stessa colonna A, ma nonostante tutti i dati siano in formato "Testo" come formato celle, nell'eliminazione degli spazi che nel vba dobbiamo identificare con la sintassi doppio apice-spazio-doppio apice (" "), venivano eliminati sì gli spazi, ma anche gli zeri iniziali dei numeri. Infatti il codice considera lo zero e il vuoto (o spazio che dir si voglia) come valori boleani simili ( cioè VERO/FALSO o anche SI/NO ),  e se  abbiamo chiesto di sostituire il vuoto (" ") con "niente vuoto" (""), eliminaniamo quindi anche lo zero iniziale, oltre agli spazi. Non ho capito questo comportamento strano, che neppure dichiarando variabili come string, o posizionando un apice nelle istruzioni, sono riuscito a risolvere, ma tantè,

L'idea di Mauro è stata quindi quella di ottenere un primo passaggio, dove eliminare tutti i segni in modo da ottenere una serie di soli numeri divisi a questo punto solo dagli spazi, quindi separare sfruttando la posizione del primo spazio di cui viene contata la "posizione" all'interno della stringa, i prefissi dai numeri, alloggiando entrambi in valori in due altre colonne , a questo punto vengono eliminati gli spazi vuoti dalla colonna numeri, e infine vengono concatenati i valori della stessa riga, ma nella colonna prefissi e quella numeri, fornendo il risultato nella colonna B. Le colonne usate come "stoccaggio" dei passaggi intermedi vengono poi "pulite" in modo che non resti traccia dei passaggi. Mauro ha realizzato il lavoro usando diverse colonne del foglio di lavoro con formule residenti per i vari passaggi, e nascondendo le colonne di "passaggio" alla fine del lavoro. Io presento una soluzione svolta soltanto tramite vba, in verde i soliti commenti e spiegazioni. Il risultato lo avete visto nell'immagine sopra, :

Sub Trasforma()
Application.ScreenUpdating = False ' per evitare saltellamenti a schermo
Dim CX As Object 'dichiarazione dela variabile CX come Object

Dim uno As String 'dichiarazione della variabile uno come Sreinga

'sotto: impostazione della variabile zonanumeri come area di lavoro per il ciclo (da A1 fino alla 'cella ultima occupata (rintracciata con End)
Set zonanumeri = ActiveSheet.Range([a1], [a1].End(xlDown))
For Each CX In zonanumeri 'per ogni cella (CX) in zonanumeri
If CX = "" Then GoTo 10 'se la cella è vuota si passa alla successiva, se occupata si prosegue
uno = CX.Value '"uno" diventa il vettore del contenuto della cella in quel momento letta
CX.Offset(0, 2) = CStr(uno) ' trasferiamo il valore di "uno" nella colonna due colonne a destra (la 'C), usando Offset. Chiaramente potremo usare colonne che siano libere, anche fuori vista, basta 'cambiare l'indice colonna 2 nella funzione Offset. il valore viene passato come stringa (CStr).
'sotto: ora che abbiamo copiato A1 (prima cella scorsa dal ciclo) in C1 usiamo l'istruzione With 'per usare il metodo Replace senza ripetere l'oggetto "espressione", e togliamo in sequenza i punti, 'le barre e le virgole dal contenuto della cella C1, sostituendole con altrettanti spazi vuoti
With CX.Offset(0, 2)
.Replace What:=".", Replacement:=" "
.Replace What:="/", Replacement:=" "
.Replace What:=",", Replacement:=" "
End With
'avremmo potuto sostituire i segni sopra visti non con uno spazio vuoto, ma eliminando 'completamente lo spazio con l'istruzione Replacement:="", ma i numeri come nella riga 18, '(vedi immagine sopra) che portano una virgola al quarto posto, ma senza spazi prima, vengono 'inesorabilmente visti come parte numerica e verrebbero resi senza lo zero iniziale.
W = CX.Offset(0, 2) ' con la variabile W prendiamo il contenuto della stessa cella alla quale 'tramite WorksheetFunction applichiamo la funzione Trim per eliminare gli spazzi iniziali e finali '(ricordo che gli spazi intermedi ci sono ancora e non li togliamo con Trim)
CX.Offset(0, 2) = WorksheetFunction.Trim(W)

'poi con la variabile X prendiamo la posizione del primo spazio usando InStr (vedi articolo in 'questa sezione "Cercare una parola") nella cella in quel momento spazzolata. Cioè prendiamo un 'numero che corrisponde alla posizione del primo spazio libero trovato nella stringa
X = InStr(CX.Offset(0, 2), " ")
If X = 0 Then 'se il valore di X è zero, cioè non esistono spazi, copiamo in contenuto della cella 'C1 (ora letta) in B1 che è la colonna di destinazione delle trasformazioni.
CX.Offset(0, 1) = CX.Offset(0, 2)
CX.Offset(0, 2).ClearContents 'poi puliamo la cella C1
GoTo 10 'e si passa a Next che ripeterà le istruzioni per la cella A2
End If

'se invece X è maggiore di zero, quindi esiste uno spazio, le istruzioni proseguono, ed estraiamo 'con Mid, tramite la variabile Y, la parte di stringa antecedente lo spazio, e con Z la parte oltre lo 'spazio, (vedi immagini sotto per visualizzare la situazione)
Y = Mid(CX.Offset(0, 2), 1, X - 1)
Z = Mid(CX.Offset(0, 2), X + 1)
CX.Offset(0, 3) = Y 'con offset ci spostiamo alla terza colonna dopo la A, quindi la D, e ci 'scriviamo il contenuto di Y
CX.Offset(0, 4) = Z 'lo stesso facciamo con Z ma spostandoci alla colonna E (la quarta dpo la A)

'sotto: ora in questa cella eliminiamo tutti gli spazi esistenti con Replace
CX.Offset(0, 4).Replace What:=" ", Replacement:=""
'ora concateniamo i contenuti delle due celle D e E e li rendiamo uniti nella cella della colonna B
CX.Offset(0, 1).Value = CX.Offset(0, 3) & CX.Offset(0, 4)
CX.Offset(0, 2).ClearContents 'quindi puliamo le celle C-D-E
CX.Offset(0, 3).ClearContents
CX.Offset(0, 4).ClearContents

10:
Next 'e si passa alla cella successiva spazzolata dal ciclo For Each, ripetendo le istruzioni fino alla 'fine delle celle occupate

End Sub

Questa è la situazione dei passaggi spiegati nelle istruzioni:

immagine a sinistra

  • colonna C - risultato dopo la sostituzione dei segni.

  • colonna  D ed E - risultato dopo la separazione con Mid dei due componenti: prefisso e numero e prima dell'applicazione del metodo Replace alla colonna E (la quarta).

immagine a destra:

  • colonna E dopo l'applicazione di Replace, e prima della concatenazione che renderà le due stringhe in B (immagine sopra)

La riga vuota che vediamo è la riga 10, il cui contenuto essendo un numero intero senza segni nè spazi, è già stato copiato in B10 tramite l'istruzione If X = 0 then ecc. ecc.

Buon lavoro, e bravo e un grazie a Mauro.

prelevato sul sito www.ennius.altervista.org