hur man delar upp textsträng i Excel med kommatecken, mellanslag, tecken eller mask
handledningen förklarar hur man delar celler i Excel med formler och Split Text-funktionen. Du kommer att lära dig att separera text med kommatecken, utrymme eller någon annan avgränsare, och hur man delar strängar i text och siffror.
delning av text från en cell i flera celler är uppgiften som alla Excel-användare har att göra med då och då., I en av våra tidigare artiklar diskuterade vi hur man delar celler i Excel med hjälp av funktionen Text till kolumn, Flash Fill och Split Names add-in. Idag ska vi ta en djupgående titt på hur du kan dela strängar med hjälp av formler och Split Text-funktionen.,
- hur man delar celler i Excel med hjälp av formler
- Split string med kommatecken, kolon, snedstreck, streck eller annan avgränsare
- Split string by line break
- formler för att dela text och siffror
- hur man skiljer celler med Split Text feature
- Split cell by character
- Split cell by string
- Split cell by mask (pattern)
- Split cell by mask (pattern) /li>
hur man delar upp text i Excel med hjälp av formler
för att dela strängen i Excel använder du vanligtvis vänster, höger eller mitten av funktionen i kombination med antingen Sök eller sök., Vid första anblicken kan några av formlerna se komplexa ut, men logiken är faktiskt ganska enkel, och följande exempel ger dig några ledtrådar.
dela sträng med kommatecken, kolon, snedstreck, streck eller annan avgränsare
när celler delas upp i Excel är nyckeln till att lokalisera avgränsarens position i textsträngen. Beroende på din uppgift kan detta göras genom att använda antingen fall-okänslig sökning eller skiftlägeskänslig sökning. När du har avgränsarens position, Använd höger, vänster eller MID-funktionen för att extrahera motsvarande del av textsträngen., För bättre förståelse, låt oss överväga följande exempel.
Om du antar att du har en lista över SKUs av objektets färgstorleksmönster och du vill dela upp kolumnen i 3 separata kolumner:
så här delar du sträng för radbrytning i Excel
om du vill dela upp text i rymden använder du formler som liknar dem som visas i föregående exempel. Den enda skillnaden är att du behöver CHAR-funktionen för att leverera linjebrytningstecknet eftersom du inte kan skriva det direkt i formeln.,
Om du antar att de celler du vill dela liknar detta: 
ta formlerna från föregående exempel och ersätt ett bindestreck ( ” – ” ) med CHAR(10) där 10 är ASCII-koden för radmatning.
och så här ser resultatet ut: 
hur man delar upp text och siffror i Excel
till att börja med finns det ingen universell lösning som skulle fungera för alla alfanumeriska strängar. Vilken formel som ska användas beror på det specifika strängmönstret. Nedan hittar du formlerna för de två vanliga scenarierna.,
Split string of ”text + number” pattern
Om du antar att du har en kolumn med strängar med text och siffror kombinerade, där ett tal alltid följer text. Du vill bryta de ursprungliga strängarna så att texten och siffrorna visas i separata celler, så här:
resultatet kan uppnås på två olika sätt.,
Metod 1: Räkna siffror och extrahera så många tecken
det enklaste sättet att dela upp textsträng där nummer kommer efter text är detta:
för att extrahera siffror söker du strängen efter alla möjliga nummer från 0 till 9, får siffrorna totalt och returnerar att många tecken från slutet av strängen.,
med den ursprungliga strängen i A2 går formeln enligt följande:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
för att extrahera text beräknar du hur många texttecken strängen innehåller genom att subtrahera antalet extraherade siffror (C2) från den totala längden på den ursprungliga strängen i A2. Därefter använder du VÄNSTERFUNKTIONEN för att returnera så många tecken från början av strängen.,
=LEFT(A2,LEN(A2)-LEN(C2))
där A2 är den ursprungliga strängen, och C2 är det extraherade numret, som visas i skärmdumpen:
Metod 2: Ta reda på positionen för den första siffran i en sträng
en alternativ lösning skulle använda följande formel för att bestämma positionen för den första siffran i strängen:
p>
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
den detaljerade förklaringen av formeln”S logik finns här.,
när positionen för den första siffran hittas kan du dela upp text och siffror med hjälp av mycket enkla vänster och höger formler.
för att extrahera text:
=LEFT(A2, B2-1)
för att extrahera nummer:
=RIGHT(A2, LEN(A2)-B2+1)
där A2 är den ursprungliga strängen, och B2 är positionen för det första numret., den första siffran kan du bädda in min-formeln i vänster och höger funktioner:
formel för att extrahera text:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
formel för att extrahera siffror:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Split string of ”number + text” pattern
Om du delar upp celler där textnummer visas efter nummer kan du extrahera med följande formel:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
formeln liknar den som diskuterades i föregående exempel, förutom att du använder vänster funktion istället för höger för att få numret från strängens vänstra sida.,
När du har siffrorna, extrahera text genom att subtrahera antalet siffror från den totala längden på den ursprungliga strängen:
=RIGHT(A2,LEN(A2)-LEN(B2))
där A2 är den ursprungliga strängen och B2 är det extraherade numret, som visas i skärmdumpen nedan:
Så här kan du dela strängar i Excel med olika kombinationer av olika funktioner., Som du ser är formlerna långt ifrån uppenbara, så du kanske vill ladda ner prov Excel Split Cells arbetsbok för att undersöka dem närmare.
Om räkna ut de svårbegripliga vändningar av Excel formler är inte din favorit ockupation, du kanske gillar den visuella metoden för att dela celler i Excel, vilket visas i nästa del av denna handledning.,
så här delar du celler i Excel med funktionen Split Text
ett alternativt sätt att dela en kolumn i Excel använder funktionen Split Text som ingår i vår Ultimate Suite for Excel, vilket ger följande alternativ:
- Split cell by character
- Split cell by string
- Split cell by mask (pattern)
för att göra saker tydligare, låt oss ta en närmare titt på varje alternativ, en i taget. – herr talman!
dela celler efter tecken
Välj det här alternativet när du vill dela upp cellinnehållet vid varje förekomst av det angivna tecknet.,
För det här exemplet, låt”s ta strängarna i objektet-Färgstorlek mönster som vi använde i den första delen av denna handledning. Som du kanske kommer ihåg separerade vi dem i 3 olika kolumner med 3 olika formler. Och här ” s hur du kan uppnå samma resultat i 2 snabba steg:
- förutsatt att du har Ultimate Suite installerat, välj de celler som ska delas och klicka på ikonen Split Text på fliken Ablebits Data.,
- delningstexten öppnas till höger i Excel-fönstret, och du gör följande:
- utöka delningen efter teckengrupp och välj en av de fördefinierade avgränsarna eller skriv något annat tecken i den anpassade rutan.
- Välj om du vill dela celler i kolumner eller rader.
- granska resultatet under Förhandsgranskningssektionen och klicka på Split-knappen.
klar! Uppgiften som krävde 3 formler och 5 olika funktioner tar nu bara ett par sekunder och ett knapptryck.
dela celler med string
med det här alternativet kan du dela strängar med valfri kombination av tecken som avgränsare. Tekniskt, du dela en sträng i delar genom att använda en eller flera olika substrings som gränserna för varje del.,
till exempel, för att dela en mening med konjunktionerna ”och” och ”eller”, expandera Split by strings group och ange avgränsningssträngarna, en per rad:
som ett resultat separeras källfrasen vid varje förekomst av varje avgränsare:
och här ett annat exempel i verkligheten., Anta att du ” har importerat en kolumn med datum från en extern källa, som ser ut som följer:
5.1.2016 12:20
5.2.2016 14:50
det här formatet är inte konventionellt för Excel, och därför skulle ingen av Datumfunktionerna känna igen något av datumelementen eller tidselementen. Om du vill dela dag, månad, år, timmar och minuter i separata celler anger du följande tecken i rutan Split by strings:
- Dot (.,) för att separera dag, månad och år
- kolon (:) för att separera timmar och minuter
- utrymme för att separera datum och tid
Tryck på Split-knappen, och du kommer omedelbart att få resultatet:
dela celler med mask (mönster)
att separera en cell med mask innebär att dela upp en sträng baserat på ett mönster.
det här alternativet är mycket praktiskt när du behöver dela upp en lista med homogena strängar i vissa element eller substrings., Komplikationen är att källtexten inte kan delas vid varje förekomst av en given avgränsare, endast vid vissa specifika händelser. Följande exempel kommer att göra det lättare att förstå.
Om du antar att du har en lista med strängar extraherade från någon loggfil: 
vad du vill är att ha datum och tid, om någon, felkod och undantag detaljer i 3 separata kolumner., Du kan inte använda ett mellanslag som avgränsare eftersom det finns mellanslag mellan datum och tid, som ska visas i en kolumn, och det finns mellanslag i undantagstexten, som också ska visas i en kolumn.
lösningen delar upp en sträng med följande mask: *fel:*undantag:*
där asterisken (*) representerar ett antal tecken.
kolonerna (:) ingår i avgränsarna eftersom vi inte vill att de ska visas i de resulterande cellerna.,
och nu, expandera avsnittet Split by mask i rutan Split Text, Skriv masken i rutan Ange avgränsare och klicka på Split:
resultatet kommer att se ut så här:
en stor fördel med denna metod är flexibilitet.,du vill att de ska visas i olika kolumner, använd den här masken:
* fel: *undantag:*
översatt till vanlig engelska, instruerar masken tillägget att dela upp de ursprungliga strängarna i 4 delar:
- alla tecken före det första utrymmet som finns i strängen (datum)
- tecken mellan det första utrymmet och ordet fel: (tid)
- Text mellan FEL: och undantag: (felkod)
- allt mellan FEL: Det kommer efter undantag: (undantagstext)
Jag hoppas att du gillade det här snabba och enkla sättet att dela strängar i Excel., Om du är nyfiken på att ge det ett försök, är en utvärderingsversion tillgänglig för nedladdning nedan. Jag tackar dig för att läsa och hoppas att se dig på vår blogg nästa vecka!
tillgängliga nedladdningar
Excel Split Cells formler (.XLSX fil)
Ultimate Suite 14-dagars fullt fungerande version (.,hur man delar celler i Excel