Come dividere la stringa di testo in Excel per virgola, spazio, carattere o maschera
Il tutorial spiega come dividere le celle in Excel utilizzando le formule e la funzione Split Text. Imparerai come separare il testo da virgola, spazio o qualsiasi altro delimitatore e come dividere le stringhe in testo e numeri.
Dividere il testo da una cella in più celle è l’attività con cui tutti gli utenti di Excel hanno a che fare una volta ogni tanto., In uno dei nostri articoli precedenti, abbiamo discusso come dividere le celle in Excel utilizzando il testo a colonna caratteristica, riempimento Flash e nomi Split add-in. Oggi, ci accingiamo a dare un’occhiata approfondita a come è possibile dividere le stringhe utilizzando le formule e la funzione Split Text.,
- Come per dividere le celle in Excel utilizzando formule
- la stringa Divisa da virgola, due punti, barra, dash o un altro delimitatore
- la stringa Divisa da interruzione di riga
- Formule di dividere il testo e i numeri
- Come per separare le cellule con la Divisione del Testo caratteristica
- Dividere la cella con il carattere
- Dividere la cella con la stringa
- Dividi cella da maschera (pattern)
Come dividere il testo in Excel utilizzando formule
dividere la stringa in Excel, si utilizza in genere la SINISTRA, a DESTRA o a METÀ funzione in combinazione con uno CERCA., A prima vista, alcune delle formule potrebbero sembrare complesse, ma la logica è in realtà abbastanza semplice, e i seguenti esempi ti daranno alcuni indizi.
Dividi la stringa per virgola, due punti, barra, trattino o altro delimitatore
Quando si dividono le celle in Excel, la chiave è individuare la posizione del delimitatore all’interno della stringa di testo. A seconda dell’attività, questo può essere fatto utilizzando la RICERCA senza distinzione tra maiuscole e minuscole o la ricerca senza distinzione tra maiuscole e minuscole. Una volta che hai la posizione del delimitatore, usa la funzione DESTRA, SINISTRA o METÀ per estrarre la parte corrispondente della stringa di testo., Per una migliore comprensione, consideriamo il seguente esempio.
Supponendo di avere un elenco di SKU del pattern Item-Color-Size e di voler dividere la colonna in 3 colonne separate:
Come dividere stringa per interruzione di riga in Excel
Per dividere il testo per spazio, utilizzare formule simili a quelle mostrate nell’esempio precedente. L’unica differenza è che avrai bisogno della funzione CHAR per fornire il carattere di interruzione di riga poiché non puoi digitarlo direttamente nella formula.,
Supponendo che le celle che vuoi dividere siano simili a queste:
Prendi le formule dall’esempio precedente e sostituisci un trattino (“-“) con CHAR(10) dove 10 è il codice ASCII per il feed di riga.
Ed è così che appare il risultato:
Come dividere testo e numeri in Excel
Per cominciare, non esiste una soluzione universale che funzioni per tutte le stringhe alfanumeriche. Quale formula usare dipende dal particolare modello di stringa. Di seguito troverai le formule per i due scenari comuni.,
Split string of “text + number” pattern
Supponendo di avere una colonna di stringhe con testo e numeri combinati, dove un numero segue sempre il testo. Vuoi rompere le stringhe originali in modo che il testo e i numeri appaiano in celle separate, in questo modo:
Il risultato può essere raggiunto in due modi diversi.,
Metodo 1: Contare le cifre ed estrarre che molti caratteri
Il modo più semplice per dividere la stringa di testo in cui il numero viene dopo il testo è questo:
Per estrarre i numeri, si cerca la stringa per ogni numero possibile da 0 a 9, ottenere i numeri totali, e restituire che molti caratteri dalla fine,
Con la stringa originale in A2, la formula è la seguente:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
Per estrarre il testo, si calcola quanti caratteri di testo contiene la stringa sottraendo il numero di cifre estratte (C2) dalla lunghezza totale della stringa originale in A2. Dopo di che, si utilizza la funzione di sinistra per restituire che molti caratteri dall’inizio della stringa.,
=LEFT(A2,LEN(A2)-LEN(C2))
Dove A2 è la stringa originale e C2 è il numero estratto, come mostrato nella schermata:
Metodo 2: Trovare la posizione del 1 ° numero in una stringa
Una soluzione alternativa sarebbe quella di usare la seguente formula per determinare la posizione della prima cifra della stringa:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
La spiegazione dettagliata della formula”s logica può essere trovato qui.,
Una volta trovata la posizione della prima cifra, è possibile dividere testo e numeri utilizzando formule molto semplici SINISTRA e DESTRA.
estrarre il testo:
=LEFT(A2, B2-1)
estrarre il numero:
=RIGHT(A2, LEN(A2)-B2+1)
Dove A2 è la stringa originale, e B2 è la posizione del primo numero., la prima cifra, è possibile incorporare la formula del MIN e funzioni:
Formula per estrarre il testo:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
Formula per estrarre i numeri:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
la stringa Divisa di “numero + testo” pattern
Se siete divisione di celle, in cui il testo viene visualizzato dopo il numero, è possibile estrarre i numeri con la seguente formula:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
La formula è simile a quello descritto nell’esempio precedente, tranne che si utilizza la funzione SINISTRA invece che a DESTRA per ottenere il numero dal lato sinistro della stringa.,
una Volta che si hanno i numeri, estrarre il testo, sottraendo il numero di cifre da la lunghezza totale della stringa originale:
=RIGHT(A2,LEN(A2)-LEN(B2))
Dove A2 è la stringa originale e B2 è il numero estratto, come mostrato nella schermata sotto:
In questo modo è possibile dividere le stringhe in Excel utilizzando diverse combinazioni di funzioni diverse., Come vedi, le formule sono tutt’altro che ovvie, quindi potresti voler scaricare la cartella di lavoro di Excel Split Cells di esempio per esaminarle più da vicino.
Se capire i colpi di scena arcane di formule di Excel non è la vostra occupazione preferita, è possibile, come il metodo visivo per dividere le celle in Excel, che è dimostrato nella parte successiva di questo tutorial.,
Come dividere le celle in Excel con il Testo suddiviso funzione
Un modo alternativo per dividere una colonna in Excel utilizzando la Divisione del Testo caratteristica incluso con il nostro Ultimate Suite per Excel, che fornisce le seguenti opzioni:
- Dividere la cella con il carattere
- Dividere la cella con la stringa
- Dividi cella da maschera (pattern)
Per rendere le cose più chiare, let”s un occhiata più da vicino a ogni opzione, uno alla volta.
Dividi le celle per carattere
Scegli questa opzione ogni volta che vuoi dividere il contenuto della cella ad ogni occorrenza del carattere specificato.,
Per questo esempio, prendiamo le stringhe del modello Item-Color-Size che abbiamo usato nella prima parte di questo tutorial. Come ricorderai, li abbiamo separati in 3 diverse colonne usando 3 diverse formule. Ed ecco come puoi ottenere lo stesso risultato in 2 passaggi rapidi:
- Supponendo che tu abbia installato Ultimate Suite, seleziona le celle da dividere e fai clic sull’icona Dividi testo nella scheda Dati Ablebits.,
- Il riquadro di testo diviso si aprirà sul lato destro della finestra di Excel e si farà quanto segue:
- Espandere la divisione per gruppo di caratteri e selezionare uno dei delimitatori predefiniti o digitare qualsiasi altro carattere nella casella personalizzata.
- Scegliere se dividere le celle in colonne o righe.
- Rivedere il risultato nella sezione Anteprima e fare clic sul pulsante Dividi.
Fatto! L’attività che richiedeva 3 formule e 5 diverse funzioni ora richiede solo un paio di secondi e un clic del pulsante.
Dividi le celle per stringa
Questa opzione consente di dividere le stringhe utilizzando qualsiasi combinazione di caratteri come delimitatore. Tecnicamente, è possibile dividere una stringa in parti utilizzando una o più sottostringhe diverse come confini di ciascuna parte.,
per esempio, Per dividere una frase dalle congiunzioni “e” e “o”, espandere la Divisa da stringhe di gruppo, e immettere il delimitatore di stringhe, una per riga:
Come risultato, la fonte frase è separato ad ogni occorrenza di ogni delimitatore:
E qui un altro esempio di vita reale., Supponendo di aver importato una colonna di date da un’origine esterna, che appare come segue:
5.1.2016 12:20
5.2.2016 14: 50
Questo formato non è convenzionale per Excel e quindi nessuna delle funzioni Date riconoscerebbe nessuno degli elementi data o ora. Per dividere giorno, mese, anno, ore e minuti in celle separate, immettere i seguenti caratteri nella casella Dividi per stringhe:
- Dot (.,) per separare il giorno, il mese e l’anno
- i due punti (:) per separare le ore e i minuti
- Spazio per separare data e ora
premi il pulsante Dividi, e si ottiene immediatamente il risultato:
Dividi celle da maschera (pattern)
Separare una cella della maschera di mezzi di suddivisione di un testo in base a un modello.
Questa opzione è molto utile quando è necessario dividere un elenco di stringhe omogenee in alcuni elementi o sottostringhe., La complicazione è che il testo sorgente non può essere diviso ad ogni occorrenza di un determinato delimitatore, solo ad alcune occorrenze specifiche. Il seguente esempio renderà le cose più facili da capire.
Supponendo di avere un elenco di stringhe estratte da qualche file di registro:
Quello che vuoi è avere data e ora, se ce ne sono, codice di errore e dettagli di eccezione in 3 colonne separate., Non è possibile utilizzare uno spazio come delimitatore perché ci sono spazi tra data e ora, che dovrebbero apparire in una colonna, e ci sono spazi all’interno del testo dell’eccezione, che dovrebbero apparire anche in una colonna.
La soluzione sta dividendo una stringa con la seguente maschera: *ERROR:*Exception:*
Dove l’asterisco (*) rappresenta un numero qualsiasi di caratteri.
I due punti (:) sono inclusi nei delimitatori perché non vogliamo che appaiano nelle celle risultanti.,
E ora, espandi la sezione Dividi per maschera nel riquadro di testo Diviso, digita la maschera nella casella Inserisci delimitatori e fai clic su Dividi:
Il risultato sarà simile a questo:
Un grande vantaggio di questo metodo è la flessibilità.,si desidera vengano visualizzati in colonne diverse, uso di questa maschera:
* *ERRORE:*Eccezione:*
Tradotto in parole povere, la maschera indica l’add-in per dividere le stringhe originali in 4 parti:
- Tutti i caratteri prima del 1 ° trovato spazio all’interno della stringa (data)
- Caratteri compreso tra il 1 ° spazio e la parola di ERRORE: (il tempo)
- Testo tra ERRORE: Eccezione: (codice di errore)
- Tutto ciò che viene dopo di Eccezione: (eccezione testo)
spero che ti è piaciuto questo rapido e semplice modo per dividere le stringhe in Excel., Se siete curiosi di fare un tentativo, una versione di valutazione è disponibile per il download qui sotto. Vi ringrazio per la lettura e spero di vedervi sul nostro blog la prossima settimana!
Download disponibili
Excel Split Cells formulas (.file xlsx)
Ultimate Suite 14 giorni versione completamente funzionale (.,file zip)
- Come dividere le celle in Excel
- Come separare i nomi in Excel: dividere nome e cognome in diverse colonne
- Come dividere data e ora in Excel
- Come unire due colonne in Excel senza perdere dati