Come dividere numeri e testo da una stringa in Excel

Molte volte ricevo dati misti dal campo e dal server per l'analisi. Questi dati sono solitamente sporchi, con colonne mescolate con numeri e testo. Durante la pulizia dei dati prima dell'analisi, separo i numeri e il testo in colonne separate. In questo articolo, ti dirò come puoi farlo.

Scenario:
Quindi un nostro amico su Exceltip.com ha posto questa domanda nella sezione commenti. “Come faccio a separare i numeri che precedono un testo e alla fine del testo utilizzando Excel Formula. Ad esempio 125EvenueStreet e LoveYou3000 ecc.”

Per estrarre il testo, usiamo RIGHT, LEFT, MID e altre funzioni di testo. Abbiamo solo bisogno di conoscere il numero di testi da estrarre. E qui faremo lo stesso prima.
Estrai numero e testo da una stringa quando il numero è alla fine della stringa
Per l'esempio sopra ho preparato questo foglio. Nella cella A2, ho la stringa. Nella cella B2, voglio la parte del testo e in C2 la parte del numero.

Quindi abbiamo solo bisogno di conoscere la posizione da cui inizia il numero. Quindi useremo Left e altre funzioni. Quindi per ottenere la posizione del primo numero usiamo la seguente formula generica:
Formula generica per ottenere la posizione del primo numero nella stringa:

=MIN(RICERCA({0,1,2,3,4,5,6,7,8,9},Rif_Stringa&"0123456789")

Ciò restituirà la posizione del primo numero.
Per l'esempio sopra, scrivi questa formula in qualsiasi cella.

=MIN(RICERCA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))

Estrai parte di testo

Restituirà 15 poiché il primo numero che viene trovato è alla 15a posizione nel testo. Lo spiegherò più tardi.

Ora, per ottenere il testo, da sinistra abbiamo solo bisogno di ottenere 15-1 caratteri dalla stringa. Quindi useremo
Funzione SINISTRA per estrarre il testo.
Formula per estrarre il testo da sinistra

=SINISTRA(A5,MIN(RICERCA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)

Qui abbiamo appena sottratto 1 da qualsiasi numero restituito da MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")).
Estrai numero parte

Ora per ottenere i numeri abbiamo solo bisogno di ottenere i caratteri numerici dal primo numero trovato. Quindi calcoliamo la lunghezza totale di corda e sottrarre la posizione del primo numero trovato e aggiungere 1 ad esso. Semplice. Sì, sembra solo complesso, è semplice.
Formula per estrarre i numeri da destra

=DESTRA(A5,LUNGHEZZA(A5)-MIN(RICERCA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)

Qui abbiamo appena ottenuto la lunghezza totale della stringa utilizzando la funzione LEN e quindi sottratta la posizione del primo numero trovato e quindi aggiunto 1 ad essa. Questo ci dà il numero totale di numeri. Scopri di più qui sull'estrazione del testo utilizzando le funzioni SINISTRA e DESTRA di Excel.

Quindi la parte della funzione SINISTRA e DESTRA è semplice. La parte Tricky è MIN e SEARCH Part che ci dà la posizione del primo numero trovato. Capiamolo.
Come funziona
Sappiamo come funzionano le funzioni SINISTRA e DESTRA. Esploreremo la parte principale di questa formula che ottiene la posizione del primo numero trovato e cioè: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789 ")
La funzione SEARCH restituisce la posizione di un testo in una stringa. La funzione SEARCH('testo','stringa') accetta due argomenti, primo il testo che vuoi cercare, secondo la stringa in cui vuoi cercare.

- Qui in SEARCH, alla posizione del testo abbiamo una matrice di numeri da 0 a 9. E alla posizione della stringa abbiamo una stringa che è concatenata con "0123456789" usando & operatore. Come mai? Ti dirò.
- Ogni elemento nell'array {0,1,2,3,4,5,6,7,8,9} verrà cercato nella stringa data e restituirà la sua posizione nella stringa di forma dell'array allo stesso indice nell'array.
- Se non viene trovato alcun valore, verrà generato un errore. Quindi tutta la formula risulterà in un errore. Per evitare ciò, abbiamo concatenato i numeri "0123456789" nel testo. In modo che trovi sempre ogni numero nella stringa. Questi numeri sono alla fine quindi non causeranno alcun problema.
- Ora la funzione MIN restituisce il valore più piccolo dall'array restituito dalla funzione SEARCH. Questo valore più piccolo sarà il primo numero nella stringa. Ora usando questa funzione NUMBER e LEFT e RIGHT, possiamo dividere le parti di testo e stringa.

Esaminiamo il nostro esempio. In A5 abbiamo la stringa che ha il nome della via e il numero civico. Dobbiamo separarli in celle diverse.
Per prima cosa vediamo come abbiamo ottenuto la nostra posizione del primo numero nella stringa.

- MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): questo si tradurrà in MIN(SEARCH({0,1,2,3, 4,5,6,7,8,9}”,Monta270123456789”))

Ora, come ho spiegato, la ricerca cercherà ogni numero nell'array {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 e restituirà la sua posizione sotto forma di array. L'array restituito sarà {8,9,6,11,12,13,14,7,16,17}. Come?
0 verrà cercato nella stringa. Si trova in posizione 8. Quindi il nostro primo elemento è 8. Nota che il nostro testo originale è lungo solo 7 caratteri. Prendilo. 0 non fa parte di Monta27.
Il prossimo 1 verrà cercato nella stringa e non fa parte della stringa originale e otteniamo la sua posizione 9.
Verranno cercati i successivi 2. Poiché è la parte della stringa originale, otteniamo il suo indice come 6.
Allo stesso modo ogni elemento si trova in una certa posizione.

- Ora questo array viene passato alla funzione MIN come MIN({8,9,6,11,12,13,14,7,16,17}). MIN restituisce il 6 che è la posizione del primo numero trovato nel testo originale.
  E la storia dopo questo è abbastanza semplice. Usiamo questo numero per estrarre testo e numeri usando le funzioni SINISTRA e DESTRA.

Estrai numero e testo da una stringa quando il numero è all'inizio della stringa
Nell'esempio precedente, Number era alla fine della stringa. Come estraiamo il numero e il testo quando il numero è all'inizio.

Ho preparato una tabella simile a quella sopra. Ha solo il numero all'inizio.

Qui useremo una tecnica diversa. Conteremo la lunghezza dei numeri (che qui è 2) ed estrarremo quel numero di caratteri da sinistra di String.
Quindi il metodo è = LEFT (stringa, conteggio dei numeri)
Per contare il numero di caratteri questa è la formula.
Formula generica per contare il numero di numeri:

=SOMMA(LUNGHEZZA(stringa)-LEN(SOSTITUTO(stringa,{"0","1","2","3","4","5","6","7","8" ,"9"},""))

Qui,

- - La funzione SOSTITUISCI sostituirà ogni numero trovato con "" (vuoto). Se viene trovato un numero ti sostituito e la nuova stringa verrà aggiunta all'array, un'altra stringa originale saggia verrà aggiunta all'array. In questo modo avremo array di 10 stringhe.
  - Ora la funzione LEN restituirà la lunghezza dei caratteri in un array di quelle stringhe.
  - Quindi, dalla lunghezza delle stringhe originali, sottrarremo la lunghezza di ogni stringa restituita dalla funzione SOSTITUISCI. Questo restituirà di nuovo un array.
  - Ora SUM aggiungerà tutti questi numeri. Questo è il conteggio dei numeri nella stringa.

Estrai parte numero da stringa

Ora poiché conosciamo la lunghezza dei numeri in corda, sostituiremo questa funzione in LEFT.
Poiché abbiamo la nostra stringa un A11 nostro:

Formula per estrarre i numeri da SINISTRA

=SINISTRA(A11,SOMMA(LIM(A11)-LEN(SOSTITUTO(A11,{"0","1","2","3","4","5","6","7" ,"8","9"},""))))

Estrai parte di testo da stringa

Poiché conosciamo il numero di numeri, possiamo sottrarlo dalla lunghezza totale della stringa per ottenere numeri alfabetici nella stringa e quindi utilizzare la funzione right per estrarre quel numero di caratteri dalla destra della stringa.

Formula per estrarre il testo da DESTRA

=DESTRA(A11,LIM(A2)-SOMMA(LIM(A11)-LIM(SOSTITUTO(A11,{"0","1","2","3","4","5","6 ","7","8","9"},""))))

Come funziona
La parte principale di entrambe le formule è SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6"," 7","8","9"},""))) che calcola la prima occorrenza di un numero. Solo dopo averlo trovato, siamo in grado di dividere testo e numero utilizzando la funzione SINISTRA. Quindi capiamo questo.

- - SOSTITUTO(A11,{"0","1","2","3","4","5","6","7","8","9"},""): Questa parte restituisce un array di stringhe in A11 dopo aver sostituito questi numeri con niente/vuoto (""). Per 27Monta tornerà {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"}.
  - LEN(SOSTITUTO(A11,{"0","1","2","3","4","5","6","7","8","9"},"" )): Ora la parte SOSTITUTO è racchiusa dalla funzione LEN. Questa lunghezza di ritorno dei testi nella matrice restituita dalla funzione SOSTITUISCI. Di conseguenza, avremo {7,7,6,7,7,7,7,6,7,7}.
  - LEN(A11)-LEN(SOSTITUTO(A11,{"0","1","2","3","4","5","6","7","8","9 "},"")): qui stiamo sottraendo ogni numero restituito dalla parte sopra dalla lunghezza della stringa effettiva. La lunghezza del testo originale è 7. Quindi avremo {7-7,7-7,7-6,… .}. Infine avremo {0,0,1,0,0,0,0,1,0,0}.
  - SOMMA(LEN(A11)-LEN(SOSTITUTO(A11,{"0","1","2","3","4","5","6","7","8", "9"},""))): qui abbiamo usato SUM per sommare l'array restituito dalla parte sopra della funzione. Questo darà 2. Che è il numero di numeri nella stringa.

Ora usando questo possiamo estrarre i testi e il numero e dividerli in celle diverse. Questo metodo funzionerà con entrambi i tipi di testo, quando il numero è all'inizio e quando è alla fine. Hai solo bisogno di utilizzare le funzioni SINISTRA e DESTRA in modo appropriato.
Usa la funzione SplitNumText per dividere numeri e testi da una stringa
I metodi di cui sopra sono un po' complessi e non sono utili quando testo e numeri sono mischiati. Per dividere testo e numeri utilizzare questa funzione definita dall'utente.

Sintassi:

=SplitNumText(stringa, operazione)

Corda: La stringa che vuoi dividere.
Operazione: questo è booleano. Passa 0 o falso per ottenere la parte di testo. Per la parte numero, passa vero o qualsiasi numero maggiore di 0.
Ad esempio, se la stringa è in A20 allora,
La formula per estrarre i numeri dalla stringa è:

=SplitNumText(A20,1)

e
La formula per estrarre il testo dalla stringa è:

=SplitNumText(A20,0)

Copia sotto il codice nel modulo VBA per far funzionare la formula sopra.

Funzione SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i , 1) Else txt = txt & Mid(str, i, 1) End If Next i If op = True Then SplitNumText = num Else SplitNumText = txt End If End Funzione

Questo codice controlla semplicemente ogni carattere nella stringa, se è un numero o meno. Se è un numero, viene memorizzato nella variabile num altrimenti nella variabile txt. Se l'utente passa true per op, viene restituito num altrimenti viene restituito txt.

Questo è il modo migliore per dividere il numero e il testo da una stringa secondo me.
Se vuoi, puoi scaricare la cartella di lavoro qui.

Quindi sì ragazzi, questi sono i modi per dividere testo e numeri in celle diverse. Fammi sapere se hai dubbi o una soluzione migliore nella sezione commenti qui sotto. È sempre divertente interagire con i ragazzi.

Fare clic sul collegamento sottostante per scaricare il file di lavoro:

Dividi numero e testo da una cella

Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività
La funzione CERCA.VERT in Excel
CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel