Supponiamo che tu abbia un foglio di lavoro con molti codici prodotto nella colonna A.

Questi codici sono nel formato A4, B12, AD4, ecc. A causa di un cambiamento nel modo in cui opera la tua azienda, ti viene chiesto di modificare tutti i codici di prodotto in modo che contengano un trattino tra le lettere ei numeri.

Esistono diversi modi per eseguire questa operazione. Se la struttura dei codici prodotto è coerente, inserire i trattini è un gioco da ragazzi.

Ad esempio, se ci sarà sempre una singola lettera seguita da numeri, allora potresti usare una formula come questa:

=LEFT(A1,1) & "-" & RIGHT(A1,LEN(A1)-1)

È probabile che i tuoi dati non siano strutturati, il che significa che potresti avere una o due lettere seguite da un massimo di tre cifre. Pertanto, sia A4 che QD284 sarebbero entrambi codici prodotto validi. In questo caso, una formula di soluzione richiede un po ‘più di creatività.

Un modo per gestirlo è con una formula di matrice. Considera la seguente formula:

=REPLACE(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),0,"-")

Se i valori sono in A1-A10, puoi inserire questa formula in B1 e quindi copiarla nella colonna. Poiché è una formula di matrice, deve essere inserita premendo Ctrl + Maiusc + Invio. La formula trova la posizione del primo numero nella cella e inserisce un trattino prima di esso.

Supponiamo, a titolo di esempio, che la cella A1 contenga BR27. La parte più interna della formula, INDIRETTO (“1: 100”), converte il testo 1: 100 in un intervallo. Viene utilizzato in modo che l’inserimento o l’eliminazione di righe non influisca sulla formula. La parte successiva della formula, ROW (INDIRECT (“1: 100”)), crea essenzialmente una matrice dei valori 1-100: 1,2,3, …​, 99,100. Questo è usato per agire su ogni personaggio nella cella.

La parte successiva, MID (A1, ROW (INDIRECT (“1: 100”)), 1), si riferisce a ogni singolo carattere nella stringa. Ciò risulta nella matrice: “B”, “R”, “2” e “7”. Moltiplicando la matrice per 1 (la parte successiva della formula)

fa sì che ciascuno dei singoli caratteri venga convertito in un numero. Se il carattere non è un numero, questa conversione restituisce un errore. Nel caso della stringa da convertire (BR27), questo risulta in: #VALUE, #VALUE, 2 e 7.

Il passaggio successivo consiste nell’applicare la funzione ISERROR ai risultati della moltiplicazione. Questo converte gli errori in TRUE e i non errori in FALSE, restituendo TRUE, TRUE, FALSE e FALSE. La funzione CONFRONTA cerca nella matrice dei valori VERO e FALSO una corrispondenza esatta di FALSO. In questo esempio, la funzione CONFRONTA restituisce il numero 3, poiché il primo valore FALSO si trova nella terza posizione della matrice. A questo punto, conosciamo essenzialmente la posizione del primo numero nella cella.

L’ultima funzione è REPLACE, che viene utilizzata per inserire effettivamente il trattino nella stringa di origine, iniziando dal terzo carattere.

Come puoi vedere, la formula per eseguire la trasformazione può essere un po ‘scoraggiante da decifrare. Per coloro che sono così inclini, potrebbe essere più semplice creare una funzione definita dall’utente. La seguente macro è un esempio di quella che restituirà una stringa con il trattino al posto giusto:

Function DashIn(myText As String)

Dim i As Integer     Dim myCharCode As Integer     Dim myLength As Integer

Application.Volatile     myLength = Len(myText)

For i = 1 To myLength         myCharCode = Asc(Mid(myText, i, 1))

If myCharCode >= 48 And myCharCode <= 57 Then             Exit For         End If     Next i     If i = 1 Or i > myLength Then         DashIn = myText     Else         DashIn = Left(myText, i - 1) & "-" _           & Mid(myText, i, myLength - 1)

End If End Function

La macro esamina ogni carattere nella stringa originale e quando trova il primo carattere numerico, inserisce un trattino in quel punto. Useresti la funzione in questo modo:

=DashIn(A1)

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (2613) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui:

Inserimento di trattini tra lettere e numeri.