hoe tekstreeks in Excel splitsen op komma, spatie, teken of masker
de zelfstudie legt uit hoe cellen in Excel worden gesplitst met behulp van formules en de functie tekst splitsen. U leert hoe u tekst kunt scheiden door komma, spatie of een ander scheidingsteken en hoe u tekenreeksen kunt splitsen in tekst en getallen.
het splitsen van tekst van één cel in meerdere cellen is de taak waarmee alle Excel-gebruikers af en toe te maken hebben., In een van onze eerdere artikelen hebben we besproken hoe u cellen in Excel kunt splitsen met behulp van de functie Tekst naar kolom, Flash Fill en Split Names add-in. Vandaag gaan we een diepgaande blik werpen op hoe u strings kunt splitsen met behulp van formules en de gesplitste tekst functie.,
- splitsen van cellen in Excel met formules
- Splits een string, komma, dubbelpunt, streep, dashboard of op een andere scheidingsteken
- Split string door het afbreken van de regel
- Formules te splitsen, tekst en cijfers
- Hoe afzonderlijke cellen met de Tekst Splitsen functie
- cel Splitsen door het teken
- cel Splitsen door string
- cel Splitsen door een masker (patroon)
Hoe te splitsen tekst in Excel-formules gebruiken
op Te splitsen string in Excel, kunt u over het algemeen gebruik je de LINKS, RECHTS of het MIDDEN van de functie in combinatie met het VINDEN of ZOEKEN., Op het eerste gezicht lijken sommige formules misschien complex, maar de logica is in feite vrij eenvoudig, en de volgende voorbeelden geven je wat aanwijzingen.
splits string door komma, dubbele punt, schuine streep, streepje of ander scheidingsteken
bij het splitsen van cellen in Excel is het de sleutel om de positie van het scheidingsteken binnen de tekenreeks te vinden. Afhankelijk van uw taak, dit kan worden gedaan door het gebruik van hoofdlettergevoelige zoekopdracht of hoofdlettergevoelige zoekopdracht. Zodra u de positie van het scheidingsteken hebt, gebruikt u de functie rechts, links of midden om het overeenkomstige deel van de tekstreeks te extraheren., Voor een beter begrip, laten we eens kijken naar het volgende voorbeeld.
stel dat u een lijst met SKU ‘ s van het Item-Color-Size-patroon hebt en u de kolom in 3 afzonderlijke kolommen wilt splitsen:
hoe tekenreeks op regeleinde te splitsen in Excel
om tekst op spatie te splitsen, gebruik formules die vergelijkbaar zijn met die in het vorige voorbeeld. Het enige verschil is dat u de CHAR-functie nodig hebt om het regeleinde-teken te leveren, omdat u het niet direct in de formule kunt typen.,
stel dat de cellen die u wilt splitsen er ongeveer zo uitzien:
neem de formules uit het vorige voorbeeld en vervang een koppelteken ( ” – ” ) door CHAR(10) waarbij 10 de ASCII-code is voor Line feed.
en zo ziet het resultaat eruit:
hoe tekst en getallen in Excel te splitsen
om te beginnen is er geen universele oplossing die zou werken voor alle alfanumerieke tekenreeksen. Welke formule te gebruiken hangt af van het specifieke stringpatroon. Hieronder vindt u de formules voor de twee gangbare scenario ‘ s.,
Split string of “text + number” pattern
stel dat u een kolom van strings hebt met tekst en getallen gecombineerd, waar een nummer altijd tekst volgt. U wilt de originele tekenreeksen verbreken zodat de tekst en getallen in afzonderlijke cellen verschijnen, zoals dit:
het resultaat kan op twee verschillende manieren worden bereikt.,
Methode 1: Tel cijfers en extraheer zoveel tekens
De makkelijkste manier om tekstreeks te splitsen waar het nummer na de tekst komt is als volgt:
om getallen te extraheren, zoek je in de tekenreeks naar elk mogelijk getal van 0 tot 9, krijg je de getallen totaal, en retourneer zoveel tekens van het einde van de tekenreeks.,
met de originele tekenreeks in A2 gaat de formule als volgt:
=RIGHT(A2,SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"},""))))
om tekst te extraheren, berekent u hoeveel teksttekens de tekenreeks bevat door het aantal geëxtraheerde cijfers (C2) af te trekken van de totale lengte van de originele tekenreeks in A2. Daarna gebruik je de functie links om zoveel tekens terug te geven vanaf het begin van de tekenreeks.,
=LEFT(A2,LEN(A2)-LEN(C2))
Waar de A2 is de originele string, en de C2 is het aantal gewonnen, zoals weergegeven in de screenshot:
Methode 2: Het vinden van de positie van de 1e positie in een string
Een alternatieve oplossing zou met behulp van de volgende formule voor het bepalen van de positie van het eerste teken in de tekenreeks:
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
De gedetailleerde uitleg van de formule ‘ s logica kan hier gevonden worden.,
zodra de positie van het eerste cijfer is gevonden, kunt u tekst en getallen splitsen met behulp van zeer eenvoudige formules links en rechts.
om tekst te extraheren:
=LEFT(A2, B2-1)
om nummer te extraheren:
=RIGHT(A2, LEN(A2)-B2+1)
waarbij A2 de oorspronkelijke tekenreeks is en B2 de positie van het eerste getal is., het eerste cijfer, kunt u het embedden van de MIN formule in de LINKER en RECHTER functies:
de Formule voor het ophalen van tekst:=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
de Formule om nummers:
=RIGHT(A2,LEN(A2)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)
Split reeks van “het nummer + tekst” patroon
Als u splitsen van cellen waar de tekst die verschijnt na het nummer, kunt u de nummers met de volgende formule:
=LEFT(A2, SUM(LEN(A2) - LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, ""))))
De formule is zoals besproken in het vorige voorbeeld, behalve dat u het gebruik van de functie LINKS in plaats van RECHTS om het aantal van de linker kant van de string.,
zodra u de getallen hebt, extraheert u tekst door het aantal cijfers af te trekken van de totale lengte van de originele tekenreeks:
=RIGHT(A2,LEN(A2)-LEN(B2))
waarbij A2 de originele tekenreeks is en B2 het geëxtraheerde getal, zoals weergegeven in de onderstaande schermafbeelding:
Dit is hoe u strings in Excel kunt splitsen met behulp van verschillende combinaties van verschillende functies., Zoals u ziet, zijn de formules verre van voor de hand liggend, dus wilt u misschien de voorbeeldwerkmap Excel Split Cells downloaden om ze nader te onderzoeken.
als het uitzoeken van de geheimzinnige wendingen van Excel-formules niet uw favoriete bezigheid is, kunt u de visuele methode om cellen in Excel te splitsen leuk vinden, wat wordt aangetoond in het volgende deel van deze tutorial.,
hoe cellen in Excel te splitsen met de functie gesplitste tekst
een alternatieve manier om een kolom in Excel te splitsen is met behulp van de functie gesplitste tekst meegeleverd met onze Ultimate Suite voor Excel, die de volgende opties biedt:
- cel splitsen op teken
- cel splitsen op tekenreeks
- cel splitsen op masker (patroon)
om de dingen duidelijker te maken, laten we elke optie beter bekijken een voor een.
Cellen splitsen op teken
Kies deze optie wanneer u de celinhoud wilt splitsen bij elk voorkomen van het opgegeven teken.,
voor dit voorbeeld, neem de strings van het Item-Color-Size patroon dat we in het eerste deel van deze tutorial hebben gebruikt. Zoals u zich misschien herinnert, hebben we ze opgesplitst in 3 verschillende kolommen met behulp van 3 verschillende formules. En zo kunt u hetzelfde resultaat bereiken in 2 snelle stappen:
- ervan uitgaande dat U Ultimate Suite hebt geïnstalleerd, selecteert u de cellen die u wilt splitsen en klikt u op het pictogram Tekst splitsen op het tabblad Ablebits Data.,
- het deelvenster gesplitste tekst wordt aan de rechterkant van uw Excel-venster geopend en u doet het volgende:
- vouw de groep gesplitst op teken uit en selecteer een van de vooraf gedefinieerde scheidingstekens of typ een ander teken in het vak aangepast.
- kies of u cellen wilt splitsen in kolommen of rijen.
- Bekijk het resultaat onder de Preview sectie en klik op de Split knop.
klaar! De taak die vereist 3 formules en 5 verschillende functies nu duurt slechts een paar seconden en een klik op de knop.
Cellen splitsen door tekenreeks
met deze optie kunt u tekenreeksen splitsen met behulp van een combinatie van tekens als scheidingsteken. Technisch gezien splits je een string in delen door een of meerdere verschillende substrings te gebruiken als de grenzen van elk deel.,
bijvoorbeeld, om een zin te splitsen door de voegwoorden “en” EN “or”, vouwt u de groep splitsen door tekenreeksen uit en voert u de scheidingstekens in, één per regel:
als gevolg hiervan wordt de bronzin gescheiden bij elk voorkomen van elk scheidingsteken:
en hier nog een voorbeeld uit het echte leven., Stel dat u een kolom met datums uit een externe bron hebt geïmporteerd, die er als volgt uit ziet:
5.1.2016 12:20
5.2.2016 14:50
Dit formaat is niet conventioneel voor Excel, en daarom herkent geen van de datumfuncties de datum-of tijdelementen. Om Dag, Maand, Jaar, uren en minuten in afzonderlijke cellen te splitsen, voert u de volgende tekens in in het vak splitsen op tekenreeksen:
- Dot (.,) om dag, maand en jaar van elkaar te scheiden
- dubbele punt (:) om uren en minuten van elkaar te scheiden
- Spatie om datum en tijd van elkaar te scheiden
druk op de knop Splitsen, en u krijgt onmiddellijk het resultaat:
Gespleten cellen per masker (pattern)
een cel scheiden met masker betekent een tekenreeks splitsen op basis van een patroon.
deze optie is erg handig wanneer u een lijst van homogene strings moet splitsen in sommige elementen of substrings., De complicatie is dat de brontekst niet kan worden gesplitst bij elk voorkomen van een bepaald scheidingsteken, alleen bij een of meer specifieke voorkomen(en). Het volgende voorbeeld zal de dingen gemakkelijker te begrijpen maken.
stel dat u een lijst met tekenreeksen hebt die uit een logbestand zijn geëxtraheerd:
wat u wilt is dat Datum en tijd, indien van toepassing, foutcode en uitzonderingsdetails in 3 afzonderlijke kolommen staan., U kunt geen spatie als scheidingsteken gebruiken omdat er spaties zijn tussen datum en tijd, die in één kolom zouden moeten verschijnen, en er spaties zijn binnen de uitzonderingstekst, die ook in één kolom zou moeten verschijnen.
de oplossing splitst een tekenreeks met het volgende masker: *ERROR:*Exception:*
waarbij de sterretje (*) een willekeurig aantal tekens vertegenwoordigt.
De dubbele punten (:) zijn opgenomen in de scheidingstekens omdat we niet willen dat ze in de resulterende cellen verschijnen.,
en vouw nu de sectie splitsen op masker uit in het deelvenster tekst splitsen, typ het masker in het vak scheidingstekens invoeren en klik op Splitsen:
het resultaat zal er ongeveer zo uitzien:
een groot voordeel van deze methode is flexibiliteit.,u wilt dat ze in verschillende kolommen verschijnen, gebruik dan dit masker:
* *ERROR:*Exception:*
vertaald in gewoon Engels, het masker instrueert de invoegtoepassing om de originele tekenreeksen in 4 delen te verdelen:
- alle tekens vóór de 1e spatie in de tekenreeks (datum)
- tekens tussen de 1e spatie en het woord fout: (tijd)
- tekst tussen fout: en Exception: (foutcode)
- alles wat na Exception komt: (Exception text)
Ik hoop dat je deze snelle en eenvoudige manier om strings in Excel te splitsen leuk vond., Als u nieuwsgierig bent om het eens te proberen, een evaluatieversie is beschikbaar voor download hieronder. Ik dank u voor het lezen en hoop u te zien op onze blog volgende week!
beschikbare downloads
Excel Split Cells formules (.xlsx file)
Ultimate Suite 14-daagse volledig functionele versie (.,zip-bestand)
- Hoe cellen in Excel te splitsen
- Hoe namen in Excel te scheiden: splits voor – en achternaam in verschillende kolommen
- Hoe datum en tijd in Excel te splitsen
- Hoe twee kolommen in Excel samen te voegen zonder gegevens te verliezen