Come dividere numeri e testo da stringa in Excel
Molte volte ottengo dati misti dal campo e dal server per l’analisi. Questi dati sono generalmente 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 separo i numeri che precedono un testo e alla fine del testo usando Excel Formula. Ad esempio 125EvenueStreet e LoveYou3000 ecc. ” Per estrarre il testo, usiamo RIGHT, LEFT, MID e altre funzioni di testo. Dobbiamo solo 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 di testo e in C2 la parte numero.
Quindi dobbiamo solo 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(SEARCH({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")
Ciò restituirà la posizione del primo numero.
Per l’esempio precedente scrivi questa formula in qualsiasi cella.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))
Estrai la parte di testo
Restituirà 15 poiché il primo numero trovato è al 15 ° posto nel testo. Lo spiegherò più tardi.
Ora, per ottenere il testo, da sinistra dobbiamo solo ottenere 15-1 caratteri dalla stringa. Quindi useremo link: / excel-text-extract-text-from-a-string-in-excel-using-excels-left-and-right-function [funzione LEFT per estrarre il testo.]
Formula per estrarre il testo da sinistra
=LEFT(A5,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)
Qui abbiamo appena sottratto 1 da qualsiasi numero restituito da MIN (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9 }, A5 e “0123456789”)).
Estrai numero parte
Ora per ottenere i numeri dobbiamo solo ottenere i caratteri numerici dal primo numero trovato. Quindi calcoliamo la lunghezza totale della stringa e sottraiamo la posizione del primo numero trovato e aggiungiamo 1 ad esso. Semplice. Sì, sembra solo complesso, è semplice.
Formula per estrarre i numeri da destra
=RIGHT(A5,LEN(A5)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)
Qui abbiamo appena ottenuto la lunghezza totale della stringa usando `link: / len-functio [funzione LEN] e poi abbiamo sottratto la posizione del primo numero trovato e poi aggiunto 1 ad esso. Questo ci dà il numero totale di numeri. Scopri di più qui su //excel-text/extract-text-from-a-string-in-excel-using-excels-left-and-right-function.html[extracting text utilizzando le funzioni LEFT e RIGHT di Excel.] `
Quindi la parte delle funzioni 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 (link: / text-excel-search-function [SEARCH] (\ {0,1,2,3,4,5,6,7,8,9}, String & “0123456789”)
La funzione link: / text-excel-search [SEARCH function] restituisce la posizione di un testo in una stringa.
SEARCH (‘text’, ‘string’) richiede due argomenti, prima il testo vuoi cercare, secondo la stringa in cui vuoi cercare.
Qui in SEARCH, nella posizione del testo abbiamo un array di numeri da 0 a 9. E nella posizione della stringa abbiamo una stringa che è concatenata con ” 0123456789 “usando l’operatore & *. Perché? Te lo dirò.
-
Ogni elemento dell’array \ {0,1,2,3,4,5,6,7,8,9} lo farà essere cercato nella stringa data e restituirà la sua posizione in forma di matrice stringa allo stesso indice nell’array.
-
Se non viene trovato alcun valore, causerà un errore. Quindi tutte le formule risulteranno in un errore. evitatelo, abbiamo concatenato i numeri “0123456789” nel testo. In modo che trovi sempre ogni numero nella stringa.
Questi numeri sono alla fine quindi non causerà 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 NUMERO e SINISTRA e DESTRA, 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 in array \ {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 e restituirà la sua posizione in 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 una parte di Monta27. Il prossimo 1 verrà cercato in stringa e non fa parte della stringa originale, e otteniamo la posizione 9.
Verranno cercati i prossimi 2. Poiché è la parte della stringa originale, otteniamo il suo indice come 6.
Allo stesso modo ogni elemento si trova in una posizione.
-
Ora questo array viene passato a
link: / statistics-excel-min-function [MIN function]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 la funzione SINISTRA e DESTRA.
Estrai numero e testo da una stringa quando Number è all’inizio di una stringa Nell’esempio precedente, Number era alla fine della stringa. Come estraiamo numero e testo quando il numero è all’inizio.
Ho preparato una tabella simile come sopra. Ha solo un numero all’inizio.
Qui useremo una tecnica diversa. Conteremo la lunghezza dei numeri (che qui è 2) ed estrarremo quel numero di caratteri dalla 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:
=SUM(LEN(string)-LEN(SUBSTITUTE(string,{"0","1","2","3","4","5","6","7","8","9"},""))
Qui, ** link: / excel-text-formas-excel-substitute-function [SOSTITUISCI funzione] sostituirà ogni numero trovato con “” (vuoto). Se viene trovato un numero che viene sostituito e una nuova stringa verrà aggiunta all’array, un’altra saggia stringa originale verrà aggiunta all’array. In questo modo avremo un 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, sottraeremo la lunghezza di ciascuna stringa restituita dalla funzione SOSTITUISCI. Ciò restituirà nuovamente un array.
Ora SUM aggiungerà tutti questi numeri. Questo è il conteggio dei numeri in stringa.
Estrai la parte numerica dalla stringa
Ora poiché conosciamo la lunghezza dei numeri in stringa, sostituiremo questa funzione in LEFT.
Dato che abbiamo la nostra stringa un A11 il nostro:
Formula per estrarre i numeri da SINISTRA
=LEFT(A11,SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Estrai la parte di testo dalla stringa
Poiché conosciamo il numero di numeri, possiamo sottrarlo dalla lunghezza totale della stringa per ottenere gli alfabeti numerici in stringa e quindi utilizzare la funzione destra per estrarre quel numero di caratteri dalla destra della stringa.
Formula per estrarre il testo da DESTRA
=RIGHT(A11,LEN(A2)-SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))))
Come funziona
La parte principale in entrambe le formule è SOMMA (LEN (A11) -LEN (SOSTITUISCI (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.
-
SOSTITUISCI (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 restituirà \ {“27Monta”, “27Monta”, “7Monta”, “27Monta”, “27Monta”, “27Monta”, “27Monta”, “2Monta”, “27Monta”, “27Monta”}.
LEN (SOSTITUISCE (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}, ” “)):
Ora la parte SOSTITUISCI è racchiusa dalla funzione LEN. Questa lunghezza di ritorno di testi in array restituiti dalla funzione SOSTITUISCI. Di conseguenza, avremo \ {7,7,6,7,7,7,7,6,7,7}.
LEN (A11) -LEN (SOSTITUISCE (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}.
SUM (LEN (A11) -LEN (SOSTITUISCI (A11, \ {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8” , “9”}, “”))):
Qui abbiamo usato SUM per sommare l’array restituito dalla parte precedente 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 funziona 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 precedenti sono un po ‘complessi e non sono utili quando testo e numeri sono mescolati. Per dividere testo e numeri utilizzare questa funzione definita dall’utente.
Sintassi:
=SplitNumText(string, op)
Stringa: la stringa che vuoi dividere.
Op: questo è booleano. Passa 0 o false per ottenere la parte di testo. Per la parte numerica, passare 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 il codice sottostante nel modulo VBA per far funzionare la formula sopra.
Function 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 Function
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 numero e testo da una stringa secondo me.
Puoi scaricare la cartella di lavoro qui, se lo desideri.
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:
`link: /wp-content-uploads-2019-11-Split-Number-and-Text-from-A-Cell.xls [__ Dividi numero e testo da una cella]
Articoli correlati:
link: / mail-send-and-receive-in-vba-how-to-extract-domain-name-from-email-in-excel [How To Extract Domain Name from EMail in Excel]
link: / excel-text-formas-split-numbers-and-text-from-string-in-excel [Dividi numeri e testo dalla stringa in Excel]
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-scorciatoie-per-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività]
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel]
link: / tips-countif-in-microsoft-excel [CONTA.SE in Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]