Tijd besparen met tekstbewerkingen in Excel

Microsoft Excel is een steunpilaar voor iedereen die met veel cijfers moet werken, van studenten tot accountants. Maar zijn bruikbaarheid gaat verder dan grote databases Excel Vs. Access - Kan een spreadsheet een database vervangen? Excel Vs. Access - Kan een spreadsheet een database vervangen? Welke tool moet u gebruiken om gegevens te beheren? Toegang en Excel zijn beide voorzien van gegevensfiltering, sortering en query's. We laten u zien welke het beste geschikt is voor uw behoeften. Lees verder ; het kan ook veel geweldige dingen met tekst doen. De onderstaande functies helpen u bij het analyseren, bewerken, converteren en anderszins wijzigen van tekst en besparen u vele uren saai en repetitief werk.

Navigatie: niet-destructieve bewerking | Tekens met halve en volle breedte | Karakter functies | Tekstanalysefuncties | Tekstconversiefuncties | Tekstbewerkingsfuncties | Tekst vervangende functies | Text Piecing-functies | Een echt wereldvoorbeeld

Niet-destructieve bewerking

Een van de principes van het gebruik van Excel-tekstfuncties is die van niet-destructieve bewerking. Simpel gezegd betekent dit dat wanneer u een functie gebruikt om een ​​wijziging in de tekst in een rij of kolom aan te brengen, die tekst ongewijzigd blijft en de nieuwe tekst in een nieuwe rij of kolom wordt geplaatst. In het begin kan dit enigszins verwarrend zijn, maar het kan erg waardevol zijn, vooral als je werkt met een enorme spreadsheet die moeilijk of onmogelijk te reconstrueren is als een bewerking fout gaat.

Terwijl u door kunt gaan met het toevoegen van kolommen en rijen aan uw immer uitbreidende gigantische spreadsheet, kunt u hiervan profiteren door uw oorspronkelijke spreadsheet op te slaan in het eerste blad van een document en vervolgens bewerkte kopieën in andere bladen. Op die manier heeft u, ongeacht het aantal bewerkingen dat u uitvoert, altijd de originele gegevens waar u aan werkt.

Karakters voor de helft en de volledige breedte

Sommige van de functies die hier worden besproken, verwijzen naar sets met tekens in één en twee bytes. Voordat we beginnen, is het handig om duidelijk te maken wat die functies zijn. In sommige talen, zoals Chinees, Japans en Koreaans, heeft elk teken (of een aantal tekens) twee weergavemogelijkheden: een teken dat is gecodeerd in twee bytes (ook wel bekend als een teken met de volledige breedte) en een teken dat is gecodeerd in een enkele byte (halve breedte). U ziet het verschil in deze tekens hier:

Zoals u kunt zien, zijn de double-byte tekens groter en, vaak, gemakkelijker te lezen. In sommige computersituaties is echter een van de andere coderingen vereist. Als je niet weet wat dit betekent of waarom je je hier zorgen over moet maken, is het zeer waarschijnlijk dat je daar niet aan hoeft te denken. In het geval dat u dit doet, zijn er echter functies opgenomen in de volgende secties die specifiek betrekking hebben op tekens met halve breedte en volledige breedte.

Karakter functies

Het komt niet vaak voor dat je in Excel met enkele karakters werkt, maar die situaties komen soms wel eens voor. En wanneer ze dat doen, zijn deze functies degene die u moet kennen.

De CHAR- en UNICHAR-functies

CHAR neemt een tekennummer en retourneert het bijbehorende teken; als je een lijst hebt met tekennummers, bijvoorbeeld, zal CHAR je helpen ze om te zetten in de karakters waarmee je meer gewend bent. De syntaxis is vrij eenvoudig:

= CHAR ([text])

[tekst] kan de vorm aannemen van een celverwijzing of een teken; so = CHAR (B7) en = CHAR (84) werken allebei. Merk op dat wanneer u CHAR gebruikt, het de codering gebruikt die op uw computer is ingesteld; dus jouw = CHAR (84) kan anders zijn dan de mijne (vooral als je op een Windows-computer zit, omdat ik Excel voor Mac gebruik).

Als het getal waarnaar u converteert een Unicode-tekennummer is en u Excel 2013 gebruikt, moet u de UNICHAR-functie gebruiken. Eerdere versies van Excel hebben deze functie niet.

De CODE- en UNICODE-functies

Zoals je zou verwachten, doen CODE en UNICODE precies het tegenovergestelde van de CHAR- en UNICHAR-functies: ze nemen een teken en retourneren het nummer voor de codering die je hebt gekozen (of die is ingesteld als standaard op je computer). Een belangrijk ding om in gedachten te houden is dat als je deze functie uitvoert op een string die meer dan één karakter bevat, het alleen de karakterreferentie voor het eerste karakter in de string zal teruggeven. De syntaxis lijkt erg op elkaar:

= CODE ([text])

In dit geval is [tekst] een teken of een tekenreeks. En als u de Unicode-referentie wilt in plaats van de standaardwaarde van uw computer, gebruikt u UNICODE (nogmaals, als u Excel 2013 of hoger hebt).

Tekstanalysefuncties

De functies in dit gedeelte helpen u informatie over de tekst in een cel te krijgen, wat in veel situaties nuttig kan zijn. We beginnen met de basis.

De LEN-functie

LEN is een heel eenvoudige functie: het retourneert de lengte van een tekenreeks. Dus als u het aantal letters in een aantal verschillende cellen moet tellen, is dit de juiste keuze. Dit is de syntaxis:

 = LENGTE ([text])

Het [tekst] -argument is de cel of cellen die u wilt tellen. Hieronder ziet u dat de LEN-functie wordt gebruikt in een cel die de plaatsnaam bevat “Austin,” het levert 6 op. Wanneer het wordt gebruikt op de plaatsnaam “South Bend,” het retourneert 10. Een spatie telt als een personage met LEN, dus houd daar rekening mee als je het gebruikt om het aantal letters in een gegeven cel te tellen.

De gerelateerde functie LENB doet hetzelfde, maar werkt met double-byte tekens. Als u een reeks van vier double-byte tekens met LEN zou tellen, zou het resultaat 8 zijn. Met LENB is dit 4 (als u een DBCS als standaardtaal hebt ingeschakeld).

De FIND-functie

U vraagt ​​zich misschien af ​​waarom u een functie genaamd FIND zou gebruiken als u alleen CTRL + F of Bewerken> Zoeken kunt gebruiken. Het antwoord ligt in de specificiteit waarmee u met deze functie kunt zoeken; in plaats van het gehele document te doorzoeken, kunt u kiezen bij welk teken van elke reeks het zoeken begint. De syntaxis helpt om deze verwarrende definitie op te helderen:

= FIND ([find_text], [within_text], [start_num])

[find_text] is de tekenreeks waarnaar u op zoek bent. [within_text] is de cel of cellen waarin Excel naar die tekst zoekt en [start_num] is het eerste teken waarnaar het zal kijken. Het is belangrijk op te merken dat deze functie hoofdlettergevoelig is. Laten we een voorbeeld nemen.

Ik heb de voorbeeldgegevens bijgewerkt, zodat het ID-nummer van elke student een alfanumerieke reeks van zes tekens is, die elk begint met een enkel cijfer, een M voor “mannetje,” een reeks van twee letters om het prestatieniveau van de student aan te geven (HP voor hoog, SP voor standaard, LP voor laag en UP / XP voor onbekend), en een laatste reeks van twee getallen. Laten we FIND gebruiken om elke goed presterende student te markeren. Dit is de syntaxis die we zullen gebruiken:

= FIND ("HP", A2, 3)

Dit zal ons vertellen of HP verschijnt na het derde karakter van de cel. Toegepast op alle cellen in de kolom ID, kunnen we in één oogopslag zien of de student goed presteerde of niet (merk op dat de 3 die door de functie wordt geretourneerd het teken is waarop HP wordt gevonden). FIND kan beter worden gebruikt als je een grotere verscheidenheid aan reeksen hebt, maar je krijgt het idee.

Net als bij LEN en LENB, wordt FINDB voor hetzelfde doel als FIND gebruikt, alleen met dubbel-bytetekensets. Dit is belangrijk vanwege de specificatie van een bepaald personage. Als u een DBCS gebruikt en u het vierde teken met FIND opgeeft, begint het zoeken bij het tweede teken. FINDB lost het probleem op.

Merk op dat FIND hoofdlettergevoelig is, zodat u naar een specifiek hoofdlettergebruik kunt zoeken. Als u een niet-hoofdlettergevoelig alternatief wilt gebruiken, kunt u de functie ZOEKEN gebruiken, die dezelfde argumenten gebruikt en dezelfde waarden retourneert.

De EXACTE functie

Als u twee waarden moet vergelijken om te zien of ze hetzelfde zijn, is EXACT de functie die u nodig hebt. Wanneer u EXACT twee strings toevoegt, wordt TRUE geretourneerd als ze exact hetzelfde zijn en FALSE als ze anders zijn. Omdat EXACT hoofdlettergevoelig is, wordt FALSE geretourneerd als u snaren geeft die lezen “Test” en “test.” Dit is de syntaxis voor EXACT:

= EXACT ([tekst1], [tekst2])

Beide argumenten spreken voor zich; zij zijn de snaren die u wilt vergelijken. In onze spreadsheet gebruiken we ze om twee SAT-scores te vergelijken. Ik heb een tweede rij toegevoegd en deze genoemd “Gerapporteerde.” Nu gaan we met EXACT door de spreadsheet en zien we waar de gerapporteerde score verschilt van de officiële score met de volgende syntaxis:

= EXACT (G2, F2)

Herhaling van die formule voor elke rij in de kolom geeft ons dit:

Tekstconversiefuncties

Deze functies nemen de waarden van één cel en veranderen ze in een ander formaat; bijvoorbeeld van een getal naar een tekenreeks of van een tekenreeks naar een getal. Er zijn een paar verschillende opties voor hoe je dit aanpakt en wat het exacte resultaat is.

De TEXT-functie

TEXT converteert numerieke gegevens naar tekst en biedt u de mogelijkheid deze op specifieke manieren te formatteren; dit kan handig zijn, bijvoorbeeld als u van plan bent om Excel-gegevens in een Word-document te gebruiken Excel-gegevens in een Word-document integreren Excel-gegevens in een Word-document integreren Tijdens uw werkweek zijn er waarschijnlijk heel vaak dat je merkt dat je informatie uit Excel in Word kopieert en plakt, of andersom. Dit is hoe mensen vaak geschreven rapporten produceren ... Lees meer. Laten we naar de syntaxis kijken en zien hoe u deze kunt gebruiken:

= TEXT ([tekst], [formaat])

Met het argument [format] kun je kiezen hoe je het nummer in de tekst wilt laten verschijnen. Er zijn een aantal verschillende operatoren die u kunt gebruiken om uw tekst op te maken, maar we houden ons hier aan een eenvoudige tekst (voor volledige informatie, zie de help-pagina van Microsoft Office over TEXT). TEXT wordt vaak gebruikt om geldwaarden te converteren, daarom beginnen we daarmee.

Ik heb een kolom toegevoegd met de naam “onderwijs” die een cijfer bevat voor elke student. We zullen dat nummer opmaken in een string die er een beetje meer uitziet zoals we gewend zijn aan het lezen van geldwaarden. Dit is de syntaxis die we zullen gebruiken:

= TEXT (G2, "$ #, ###")

Het gebruik van deze opmaakreeks geeft ons cijfers die worden voorafgegaan door het dollarteken en bevatten een komma na de honderdste plaats. Dit is wat er gebeurt als we het op de spreadsheet toepassen:

Elk nummer is nu correct geformatteerd. U kunt TEXT gebruiken om getallen, valutawaarden, datums en tijden op te maken en zelfs om onbeduidende cijfers te verwijderen. Raadpleeg de bovenstaande Help-pagina voor meer informatie over hoe u al deze dingen kunt doen.

De VASTE functie

Vergelijkbaar met TEXT, neemt de FIXED-functie input en formatteert deze als tekst; FIXED is echter gespecialiseerd in het converteren van getallen naar tekst en geeft u een paar specifieke opties voor het formatteren en afronden van de uitvoer. Dit is de syntaxis:

= FIXED ([nummer], [decimalen], [no_commas])

Het [nummer] -argument bevat de verwijzing naar de cel die u naar tekst wilt converteren. [decimalen] is een optioneel argument waarmee u het aantal decimalen kunt kiezen dat in de conversie wordt bewaard. Als dit 3 is, krijg je een nummer zoals 13.482. Als u een negatief getal voor decimalen gebruikt, rondt Excel het getal af. We zullen dit in het onderstaande voorbeeld bekijken. [no_commas], indien ingesteld op TRUE, sluit komma's uit van de eindwaarde.

We gebruiken dit om de collegegeldwaarden die we in het laatste voorbeeld gebruikten af ​​te ronden naar het dichtstbijzijnde duizendtal.

= VAST (G2, -3)

Wanneer toegepast op de rij, krijgen we een rij afgeronde collegegeldwaarden:

De VALUE-functie

Dit is het tegenovergestelde van de TEXT-functie: elke cel is nodig en verandert deze in een getal. Dit is vooral handig als u een spreadsheet importeert of een grote hoeveelheid gegevens kopieert en plakt en deze wordt opgemaakt als tekst. Ga als volgt te werk om het te repareren:

= VALUE ([text])

Dat is alles wat er is. Excel herkent geaccepteerde formaten van constante getallen, tijden en datums en converteert deze naar getallen die vervolgens kunnen worden gebruikt met numerieke functies en formules. Dit is een vrij eenvoudige, dus we slaan het voorbeeld over.

De DOLLAR-functie

Net als de functie TEXT, converteert DOLLAR een waarde naar tekst, maar deze voegt ook een dollarteken toe. U kunt ook kiezen uit het aantal decimalen dat u wilt opnemen:

= DOLLAR ([tekst], [decimalen])

Als u het argument [decimalen] leeg laat, wordt dit standaard ingesteld op 2. Als u een negatief getal opneemt voor het argument [decimalen], wordt het getal afgerond naar links van het decimaalteken.

De ASC-functie

Herinner je onze discussie over single- en double-byte karakters? Dit is hoe je ze omzet. In het bijzonder converteert deze functie tekens met volledige breedte en dubbele byte naar halve breedte en één byte tekens. Het kan worden gebruikt om wat ruimte in uw spreadsheet te besparen. Dit is de syntaxis:

= ASC ([text])

Best makkelijk. Voer gewoon de ASC-functie uit op elke tekst die u wilt omzetten. Om het in actie te zien, zal ik deze spreadsheet converteren, die een aantal Japanse katakana bevat: deze worden vaak weergegeven als tekens met de volledige breedte. Laten we ze veranderen in halve breedte.

De JIS-functie

Natuurlijk, als je een manier kunt converteren, kun je ook de andere manier converteren. JIS converteert tekens met halve breedte naar volle breedte. Net als ASC is de syntaxis heel eenvoudig:

 = JIS ([text])

Het idee is vrij simpel, dus we gaan verder naar het volgende gedeelte zonder een voorbeeld.

Tekstbewerkingsfuncties

Een van de meest bruikbare dingen die u met tekst in Excel kunt doen, is door het programma-matig te bewerken. De volgende functies helpen u bij het invoeren van tekst en komen in de exacte indeling die u het meest van pas komt.

De UPPER, LOWER en PROPER-functies

Dit zijn allemaal heel eenvoudige functies om te begrijpen. UPPER maakt tekst in hoofdletters, LOWER maakt het in kleine letters, en PROPER maakt de hoofdletter in de eerste letter van elk woord terwijl de rest van de letters in kleine letters worden geplaatst. Er is hier geen voorbeeld nodig, dus ik zal u de syntaxis geven:

= BOVEN / ONDER / PROPER ([text])

Kies de cel of het cellenbereik waarin uw tekst staat voor het [tekst] -argument en u bent klaar om te beginnen.

De SCHOON functie

Het importeren van gegevens in Excel gaat meestal best goed, maar soms krijg je personages die je niet wilt. Dit komt het meest voor als het originele document speciale tekens bevat die Excel niet kan weergeven. In plaats van alle cellen te doorlopen die deze tekens bevatten, kunt u de functie CLEAN gebruiken, die er als volgt uitziet:

= CLEAN ([text])

Het argument [text] is gewoon de locatie van de tekst die u wilt opruimen. In het voorbeeld van de spreadsheet heb ik een paar niet-afdrukbare tekens toegevoegd aan de namen in kolom A die moeten worden verwijderd (er is er één in rij 2 die de naam naar rechts duwt en een foutteken in rij 3) . Ik heb de functie CLEAN gebruikt om de tekst zonder deze tekens over te brengen naar kolom G:

Kolom G bevat nu de namen zonder de niet-afdrukbare tekens. Deze opdracht is niet alleen handig voor tekst; het kan u vaak helpen als nummers ook uw andere formules verpesten; speciale personages kunnen echt grote schade aanrichten met berekeningen. Het is essentieel wanneer u van Word naar Excel converteert Word naar Excel converteren: converteer uw Word-document naar een Excel-bestand Word naar Excel converteren: converteer uw Word-document naar een Excel-bestand Lees meer.

De TRIM-functie

Terwijl CLEAN niet-afdrukbare tekens verwijdert, verwijdert TRIM extra spaties aan het begin of het einde van een tekstreeks waarmee u deze kunt eindigen als u tekst vanuit een Word- of een gewoon tekstdocument kopieert en iets te zien krijgt net zoals ” Vervolgdatum ”... om het te veranderen “Vervolgdatum,” gebruik gewoon deze syntaxis:

= TRIM ([text])

Wanneer u het gebruikt, ziet u vergelijkbare resultaten als wanneer u SCHOON gebruikt.

Tekst vervangende functies

Af en toe moet je specifieke tekenreeksen in je tekst vervangen door een reeks andere tekens. Het gebruik van Excel-formules is veel sneller dan zoeken en vervangen Verover de tekstzoektaken 'Zoeken en vervangen' met wReplace Verover de 'Zoek en vervang' teksttaken met wReplace Lees meer, vooral als u met een zeer groot werkblad werkt.

De SUBSTITUUT-functie

Als u met veel tekst werkt, moet u soms enkele belangrijke wijzigingen aanbrengen, zoals het onderverdelen van één tekenreeks voor een andere tekst. Misschien besefte je dat de maand fout is in een reeks facturen. Of dat je iemands naam verkeerd hebt getypt. Hoe het ook zij, soms moet je een string vervangen. Dat is waar SUBSTITUUT voor is bedoeld. Dit is de syntaxis:

= SUBSTITUTE ([tekst], [oude_tekst], [nieuwe_tekst], [instantie])

Het [tekst] -argument bevat de locatie van de cellen waarin u de vervanging wilt uitvoeren en de [oude_tekst] en [nieuwe_tekst] zijn vrij duidelijk. [instance] laat je toe om een ​​specifiek exemplaar van de oude te vervangen tekst te specificeren. Dus als je alleen het derde exemplaar van de oude tekst wilt vervangen, zou je dit invoeren “3” voor dit argument. SUBSTITUTE kopieert alle andere waarden (zie hieronder).

Als voorbeeld corrigeren we een spelfout in onze spreadsheet. Laten we zeggen “Honolulu” was per ongeluk gespeld als “Honululu.” Dit is de syntaxis die we zullen gebruiken om dit te corrigeren:

= SUBSTITUTE (D28, "Honululu", "Honolulu")

En dit is wat er gebeurt als we deze functie uitvoeren:

Nadat u de formule naar de omliggende cellen hebt gesleept, ziet u dat alle cellen uit kolom D zijn gekopieerd, behalve de cellen met de spelfout “Honululu,” die werden vervangen door de juiste spelling.

De REPLACE-functie

REPLACE lijkt veel op SUBSTITUUT, maar vervangt een specifieke tekenreeks door tekens in een specifieke positie te vervangen. Een blik op de syntaxis maakt duidelijk hoe de functie werkt:

= REPLACE ([old_text], [start_num], [num_chars], [new_text])

[oude_tekst] is waar u de cellen wilt opgeven waarin u de tekst wilt vervangen. [start_num] is het eerste teken dat u wilt vervangen en [num_chars] is het aantal tekens dat zal worden vervangen. We zullen zien hoe dit werkt in een ogenblik. [new_text] is natuurlijk de nieuwe tekst die in de cellen zal worden ingevoegd - dit kan ook een celverwijzing zijn, wat heel nuttig kan zijn.

Laten we een voorbeeld bekijken. In onze spreadsheet hebben de student-ID's HP, SP, LP, UP en XP-reeksen. We willen ze verwijderen en ze allemaal naar NP veranderen, wat lang zou duren door gebruik te maken van SUBSTITUTE of Find and Replace. Dit is de syntaxis die we zullen gebruiken:

= REPLACE (A2, 3, 2, "NP")

Toegepast op de hele kolom, hier is wat we krijgen:

Alle twee letterreeksen uit kolom A zijn vervangen door “NP” in kolom G.

Text Piecing-functies

Naast het aanbrengen van wijzigingen in strings, kun je ook dingen doen met kleinere stukjes van die reeksen (of gebruik die reeksen als kleinere stukjes om grotere te maken). Dit zijn enkele van de meest gebruikte tekstfuncties in Excel.

De CONCATENATE-functie

Dit heb ik zelf een paar keer gebruikt. Wanneer u twee cellen hebt die moeten worden bij elkaar opgeteld, is CONCATENATE uw functie. Dit is de syntaxis:

= CONCATENATE ([text1], [text2], [text3] ...)

Wat concatenate zo nuttig maakt, is dat de [text] -argumenten gewone tekst kunnen zijn, zoals “Arizona,” of celverwijzingen zoals “A31.” Je kunt zelfs de twee mixen. Dit kan u enorm veel tijd besparen wanneer u twee tekstkolommen moet combineren, bijvoorbeeld als u een. Wilt maken “Voor-en achternaam” kolom van a “Voornaam” en een “Achternaam” kolom. Dit is de syntaxis die we gebruiken om dat te doen:

= CONCATENATE (A2, "", B2)

Merk op dat het tweede argument een lege ruimte is (getypt als aanhalingsteken-spatie-qoutation-markering). Zonder dit zouden de namen direct worden samengevoegd, zonder ruimte tussen voor- en achternamen. Laten we eens kijken wat er gebeurt als we dit commando uitvoeren en autofill gebruiken voor de rest van de kolom:

Nu hebben we een kolom met de volledige naam van iedereen. U kunt deze opdracht eenvoudig gebruiken om netnummers, telefoonnummers, namen en werknemersnummers, steden en staten of zelfs valutatekens en bedragen te combineren.
U kunt de CONCATENATE-functie in de meeste gevallen verkorten tot één en-teken. Als u de bovenstaande formule wilt maken met het en-teken, typen we dit:

= A2 & "" & B2

Je kunt het ook gebruiken om celverwijzingen en tekstregels te combineren, zoals dit:

= E2 & "," & F2 & ", VS"

Dit neemt de cellen met namen van steden en staten en combineert ze met “Verenigde Staten van Amerika” om het volledige adres te krijgen, zoals hieronder te zien is.

De LEFT en RIGHT functies

Vaak wilt u alleen met de eerste (of laatste) paar tekens van een tekstreeks werken. Met LINKS of RECHTS kun je dat doen door slechts een bepaald aantal tekens terug te geven, beginnend bij het linker- of rechtse teken in een tekenreeks. Dit is de syntaxis:

= LINKS / RECHTS ([tekst], [num_chars])

[tekst] is natuurlijk de originele tekst en [num_chars] is het aantal tekens dat u wilt retourneren. Laten we eens kijken naar een voorbeeld van wanneer je dit zou willen doen. Laten we zeggen dat u een aantal adressen hebt geïmporteerd en elk bevat zowel de afkorting als het land. We kunnen LEFT gebruiken om alleen de afkortingen te krijgen, met behulp van deze syntaxis:

= LINKS (E2, 2)

Dit is wat dat lijkt op onze spreadsheet:

Als de afkorting na de staat was gekomen, zouden we RECHTS op dezelfde manier hebben gebruikt.

De MID-functie

MID lijkt veel op LINKS en RECHTS, maar laat je tekens uit het midden van een reeks trekken, beginnend op een positie die je opgeeft. Laten we de syntaxis eens bekijken om te zien hoe het werkt:

= MID ([text], [start_num], [num_chars])

[start_num] is het eerste teken dat wordt geretourneerd. Dit betekent dat als u wilt dat het eerste teken in een tekenreeks wordt opgenomen in het resultaat van een functie, dit dit is “1.” [num_chars] is het aantal tekens na het startteken dat zal worden geretourneerd. We zullen hiermee een beetje tekst opschonen. In het voorbeeld van de spreadsheet hebben we nu titels toegevoegd aan de achternaam, maar we willen deze graag verwijderen zodat een achternaam van “Meneer Martin” zal worden geretourneerd als “Martin.” Dit is de syntaxis:

= MID (A2, 5, 15)

We zullen gebruiken “5” als het startteken, omdat de eerste letter van de naam van de persoon het vijfde teken is (“Dhr. ” neemt vier spaties in beslag). De functie retourneert de volgende 15 letters, wat genoeg zou moeten zijn om het laatste deel van iemands naam niet af te snijden. Dit is het resultaat in Excel:

In mijn ervaring vind ik MID het meest nuttig wanneer je het combineert met andere functies. Laten we zeggen dat deze spreadsheet, in plaats van alleen mannen te omvatten, ook vrouwen omvatte, die beide konden hebben “Mevrouw.” of “Mvr.” voor hun titels. Wat zouden we dan doen? Je kunt MID combineren met IF om de voornaam te krijgen, ongeacht de titel:

= IF (LINKS (A2, 3) = "Mevrouw", MID (A2, 6, 16), MID (A2, 5, 15)

Ik laat je precies zien hoe deze formule zijn magie uitwerkt (je moet wellicht de booleaanse operatoren van Excel bekijken Mini Excel-zelfstudie: Gebruik Boolean Logic om complexe gegevens te verwerken Mini Excel-zelfstudie: Gebruik Boolean Logic om complexe gegevenslogische operatoren te verwerken INDIEN, NIET EN, en OF, kunnen u helpen om van Excel newbie naar hoofdgebruiker te komen We leggen de basisprincipes van elke functie uit en laten zien hoe u ze kunt gebruiken voor maximale resultaten..

De REPT-functie

Als je een string moet nemen en het een aantal keren moet herhalen, en je wilt het liever niet steeds opnieuw typen, kan REPT helpen. Geef REPT een string (“abc”) en een aantal (3) keer dat u het herhaald wilt hebben, en Excel geeft u precies waar u om vroeg (“abcabcabc”). Hier is de zeer eenvoudige syntaxis:

= REPT ([tekst], [nummer])

[tekst] is duidelijk de basisreeks; [nummer] is het aantal keren dat u het wilt herhalen. Hoewel ik nog geen goed gebruik van deze functie tegengekomen ben, weet ik zeker dat iemand daar iets voor zou kunnen gebruiken. We zullen een voorbeeld gebruiken dat, hoewel het niet echt nuttig is, u het potentieel van deze functie zou kunnen laten zien. We gaan REPT combineren met “&” om iets nieuws te creëren. Dit is de syntaxis:

= "*<---" & REPT("*", 9) & "--->*"

Het resultaat wordt hieronder getoond:

Een real-world voorbeeld

Om u een idee te geven van hoe u een tekstfunctie in gebruik kunt nemen in de echte wereld, zal ik een voorbeeld geven van waar ik MID combineerde met verschillende conditionals in mijn eigen werk. Voor mijn postgraduaat psychologie heb ik een onderzoek uitgevoerd waarbij deelnemers op een van de twee knoppen moesten klikken en de coördinaten van die klik werden vastgelegd. De knop aan de linkerkant van het scherm was gemarkeerd met A en die aan de rechterkant was gemarkeerd met B. Elke proef had een goed antwoord en elke deelnemer deed 100 proeven.

Om deze gegevens te analyseren, moest ik zien hoeveel proeven elke deelnemer goed had. Hier ziet u hoe de spreadsheet met resultaten eruitzag na een beetje opschonen:

Het juiste antwoord voor elke proef wordt weergegeven in kolom D en de coördinaten van de klik worden weergegeven in de kolommen F en G (ze zijn opgemaakt als tekst, wat de zaak ingewikkelder maakt). Toen ik begon, ging ik gewoon door en deed de analyse handmatig; als kolom D zei “optiona” en de waarde in kolom F was negatief, ik zou 0 invoeren (voor “fout”). Als het positief was, zou ik 1 invoeren. Het omgekeerde was waar als kolom D las “optionb.”

Na een beetje knutselen, kwam ik op een manier om de MID-functie te gebruiken om het werk voor mij te doen. Dit is wat ik heb gebruikt:

= IF (D3 = "optiona", IF (MIDDEL (F3,2,1) = "-", 1,0), ALS (MIDDEL (F3,2,1) = "-", 0,1))

Laten we dat onderbreken. Te beginnen met de eerste IF-verklaring hebben we het volgende: “als cel D3 'optiona' zegt, dan [eerste voorwaardelijke]; zo niet, dan [tweede voorwaardelijk].” De eerste voorwaarde zegt dit: “als het tweede teken van cel F3 een koppelteken is, geeft u true terug; zo niet, retourneer dan false.” De derde zegt “als het tweede teken van cel F3 een koppelteken is, retourneert u false; zo niet, retourneer dan waar.”

Het kan een tijdje duren om je hoofd eromheen te wikkelen, maar het moet duidelijk worden. Kortom, deze formule controleert om te zien of D3 zegt “optiona”; als dit het geval is en het tweede teken van F3 een koppelteken is, wordt de functie geretourneerd “waar.” Als D3 bevat “optiona” en het tweede teken van F3 is geen koppelteken, het komt terug “vals.” Als D3 niet bevatten “optiona” en het tweede teken van F3 is een koppelteken, het komt terug “vals.” Als D3 niet zegt “optiona” en het tweede karakter van F2 is geen koppelteken, het komt terug “waar.”

Hier ziet u hoe de spreadsheet eruitziet wanneer u de formule uitvoert:

Nu de “partituur” kolom bevat een 1 voor elke proef die de deelnemer correct heeft beantwoord en een 0 voor elke proef die zij verkeerd hebben beantwoord. Van daaruit is het eenvoudig om de waarden samen te vatten om te zien hoeveel ze goed hebben gekregen.

Ik hoop dat dit voorbeeld u een idee geeft van hoe u op een creatieve manier tekstfuncties kunt gebruiken wanneer u met verschillende soorten gegevens werkt. De kracht van Excel is bijna onbeperkt 3 Crazy Excel-formules die geweldige dingen doen 3 Crazy Excel-formules die geweldige dingen doen Voorwaardelijke formatteringsformules in Microsoft Excel kunnen geweldige dingen doen. Hier volgen enkele handige trucs voor de productiviteit van de Excel-formule. Lees Meer en als je de tijd neemt om een ​​formule te bedenken die je werk voor je doet, kun je een hoop tijd en moeite besparen!

Excel-tekstbeheersing

Excel is een krachtpatser als het gaat om het werken met getallen, maar het heeft ook een verrassend aantal handige tekstfuncties. Zoals we hebben gezien, kunt u tekst analyseren, converteren, vervangen en bewerken, en deze functies combineren met andere complexe berekeningen en transformaties uitvoeren.

Van e-mail verzenden Hoe e-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken E-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken Met onze codesjabloon kunt u geautomatiseerde e-mails instellen vanuit Excel met behulp van Collaboration Data Objects (CDO) en VBA-scripts. Meer lezen over het doen van uw belastingen Uw belastingen doen? 5 Excel-formules die u moet weten Belastingen doen? 5 Excel-formules die u moet weten Het is twee dagen voordat uw belasting verschuldigd is en u geen nieuwe late indieningskosten wilt betalen. Dit is het moment om gebruik te maken van de kracht van Excel om alles op orde te krijgen. Lees meer, Excel kan u helpen uw leven lang te beheren Microsoft Excel gebruiken om uw leven te beheren Microsoft Excel gebruiken om uw leven te beheren Het is geen geheim dat ik een totale Excel-fanboy ben. Veel daarvan komt voort uit het feit dat ik geniet van het schrijven van VBA-code, en Excel gecombineerd met VBA-scripts opent een hele wereld van mogelijkheden ... Lees meer. En als u deze tekstfuncties leert gebruiken, komt u een stap dichter bij een Excel-master.

Laat ons weten hoe je tekstbewerkingen hebt gebruikt in Excel! Wat is de meest complexe transformatie die je hebt gedaan?

Ontdek meer over: Longform Guide, Microsoft Excel.