Marty ha un foglio di lavoro con una lunga serie di numeri di parte nella colonna A.

Questi consistono di lettere e numeri, come A123BC, AB123C, ecc. Marty vuole suddividere i dati in tre colonne, in modo che il testo prima dei numeri si trovi in ​​una colonna, i numeri nella seconda colonna e il testo dopo i numeri nella terzo.

Il fattore che complica la divisione del numero di parte in segmenti è che non esiste una lunghezza impostata per ciascun componente del numero di parte combinato. Se i componenti fossero di lunghezza standard, è possibile utilizzare la funzione Testo in colonne in Excel. Poiché non lo sono e non vi è alcun delimitatore tra i componenti, la potenziale via di soluzione è chiusa.

Se si desidera utilizzare formule per separare i numeri di parte, sarà necessario tre di essi, uno per ogni componente che si desidera estrarre.

Supponendo che i numeri di parte seguano lo schema indicato (testo, cifre, testo) e che il primo numero di parte si trovi nella cella A1, è possibile utilizzare quanto segue nella cella B1:

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

Questo deve essere inserito come una formula di matrice, il che significa che devi inserirlo utilizzando Ctrl + Maiusc + Invio. La formula trova la prima cifra numerica nel numero di parte, quindi restituisce tutto ciò che precede quella cifra.

Funzionerà su qualsiasi numero di parte che non superi i 100 caratteri di lunghezza.

Per estrarre il secondo componente del numero di parte, puoi inserire la seguente formula nella cella C1:

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

Ancora una volta, questa è una singola formula e deve essere inserita come una formula di matrice (Ctrl + Maiusc + Invio) in modo che possa funzionare su ogni carattere nel numero di parte originale. Esamina il numero di parte e determina il punto iniziale delle cifre, quindi estrae tutte quelle cifre. Restituisce una stringa di testo, anche se quella stringa è composta da cifre. Se vuoi che venga effettivamente trattato come un numero (che eliminerebbe gli zeri iniziali, ovviamente), allora devi racchiudere l’intera formula in una funzione Value, come mostrato qui:

=VALUE(MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0),COUNT(1MID(A1,ROW(INDIRECT("1:100")),1))))

Per ottenere l’ultimo componente del numero di parte, è necessario utilizzare la seguente formula, ancora una volta inserita come formula di matrice:

=MID(A1,MATCH(FALSE,ISERROR(1MID(A1,ROW(INDIRECT("1:100")), 1)),0)+COUNT(1MID(A1,ROW(INDIRECT("1:100")),1)),100)

Sebbene questo approccio funzioni molto bene, le formule di matrice sono notoriamente impegnative per i calcoli, specialmente quando hai molte formule nel tuo foglio di lavoro. Se devi separare un migliaio di numeri di parte, significa che ti ritroverai con 3.000 formule di array, che possono essere molto, molto lente nel ricalcolare.

Se ti trovi in ​​questa situazione, potresti voler utilizzare una macro per separare effettivamente i numeri di parte. La seguente macro dovrebbe funzionare sui numeri di parte che seguono lo schema di testo, cifre, testo, come già descritto.

Sub Split1()

Dim C As Range     Dim sNew As New     Dim i As Integer          For Each C In Selection         sNew = ""

i = 1

' Get first part, which is text         Do While IsNumeric(Mid(C, i, 1)) = False             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 1).Value = sNew

' Pull next part, which should be digits         sNew = ""

Do While IsNumeric(Mid(C, i, 1)) = True             sNew = sNew & Mid(C, i, 1)



i = i + 1             If i > Len(C) Then Exit Do         Loop         C.Offset(0, 2).Value = sNew

' Use rest of original cell         sNew = Mid(C, i, Len(C))

C.Offset(0, 3).Value = sNew     Next C End Sub

Per utilizzare la macro, è sufficiente selezionare i numeri di parte ed eseguirla. La macro utilizza il concetto di ricerca di modifiche tra valori numerici / non numerici nella stringa di caratteri nella cella. Quando viene raggiunto uno di questi limiti, la parte della stringa originale prima del confine viene inserita in una nuova cella. Questo concetto può essere un po ‘accorciato, come nell’esempio seguente.

Sub Split2()

Dim C As Range     Dim j As Integer     Dim k As Integer

For Each C In Selection         j = 1         Do While Not (IsNumeric(Mid(C.Value, j, 1))) And j <= Len(C)

j = j + 1         Loop         k = j

Do While IsNumeric(Mid(C.Value, k, 1)) And k <= Len(C)

k = k + 1         Loop

C.Offset(0, 1) = Left(C, j - 1)

C.Offset(0, 2) = Mid(C, j, k - j)

C.Offset(0, 3) = Mid(C, k, Len(C) - (k - 1))

Next C End Sub

La differenza tra questa versione della macro e quella precedente, ovviamente, è che questa versione passa attraverso la cella originale e determina i confini tutti in una volta. Quando sono noti, i componenti del numero di parte originale vengono inseriti nelle celle.

Un approccio interessante per separare i numeri di parte consiste nell’utilizzare un paio di brevi funzioni definite dall’utente che determinano dove si trovano i confini tra i componenti. Considera le seguenti due funzioni:

Function pNumber(X)

i = 1     Do Until Mid(X, i, 1) Like "#": i = i + 1: Loop     pNumber = i End Function
Function pAlpha(X)

X = UCase(X)

i = pNumber(X)

Do Until Mid(X, i, 1) Like "[A-Z]": i = i + 1: Loop     pAlpha = i End Function

Queste sono molto più brevi delle macro precedenti e tutto ciò che fanno è restituire il confine dove iniziano i numeri (nel caso di pNumber) e il confine dove inizia il secondo gruppo di testo (nel caso di pAlpha). Per utilizzare le funzioni, si utilizzano le seguenti tre formule per restituire, rispettivamente, il primo, il secondo e il terzo componente del numero di parte originale:

=MID(A1,1,pNumber(A1)-1)

=MID(A1,pNumber(A1),pAlpha(A1)-pNumber(A1))

=MID(A1,pAlpha(A1),LEN(A1)-pAlpha(A1)+1)

_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 (3141) 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:

Spezzare i numeri di parte a lunghezza variabile.