Een nummer of tekst uit Excel extraheren met deze functie

Microsoft Excel werkt uitstekend met zowel getallen als tekst Tijd besparen met tekstbewerkingen in Excel Bespaar tijd met tekstbewerkingen in Excel Excel kan magie met getallen doen en het kan evengoed met tekens omgaan. Deze handleiding laat zien hoe u tekst in spreadsheets kunt analyseren, converteren, vervangen en bewerken. Met deze basisprincipes kunt u complexe transformaties uitvoeren. Lees meer - maar als u beide in dezelfde cel gebruikt, kunt u wat problemen tegenkomen. Gelukkig kunt u getallen of tekst extraheren uit cellen om efficiënter met uw gegevens te werken. We demonstreren verschillende opties, afhankelijk van het formaat waarin uw gegevens zich momenteel bevinden.

Nummers opgeslagen als tekst

Dit is een veel voorkomende situatie en - gelukkig - heel gemakkelijk om mee om te gaan. Soms worden cellen die alleen cijfers bevatten ten onrechte als tekst gelabeld, waardoor wordt voorkomen dat Microsoft Excel deze in bewerkingen gebruikt. Je kunt in de afbeelding hieronder zien dat de cellen in kolom A zijn opgeslagen als tekst, zoals aangegeven door de groene vlag op elke cel, evenals het opmaakvak, die beide hieronder zijn gemarkeerd.

Converteren naar nummer

Om het probleem op te lossen, klikt u gewoon in een cel, klikt u op het waarschuwingsvak en selecteert u “Converteren naar nummer.” Dat is alles wat er is! Dit kan op meerdere cellen worden gedaan door ze allemaal te selecteren en op het waarschuwingsvenster te klikken, maar dit is waarschijnlijk niet zo efficiënt als de volgende methode.

Tekst naar kolommen

Als u veel cellen hebt die moeten worden hersteld, kan het gebruik van deze methode een enorme hoeveelheid tijd in beslag nemen. Om sneller door het proces te komen, kunt u de tekst naar kolommen van Microsoft Excel gebruiken Tekstbestanden met scheidingstekens converteren naar Excel-spreadsheets Losse tekstbestanden naar Excel-spreadsheets converteren Gebruik Microsoft Excel om gegevens uit een tekstbestand in een werkblad te importeren. Met de wizard Tekst importeren kunt u gegevens uit gescheiden tekst importeren en netjes ordenen. Lees Meer functie. Selecteer de nummers die u wilt converteren, ga naar Gegevens> Tekst naar kolommen, en gebruik de wizard om ervoor te zorgen dat de cijfers correct worden weergegeven (voor het grootste deel moet je gewoon klikken volgende en Af hebben zonder knoeien met een van de instellingen).

De tekst wordt geconverteerd naar getallen en u bent klaar om te beginnen. Merk op dat dit maar op één kolom tegelijk werkt, dus als je veel kolommen hebt, wil je misschien met deze laatste methode gaan.

Plak speciaal

Om deze methode te laten werken, moet je nummer 1 in een cel invoeren (het is belangrijk dat deze in getalnotatie staat). Selecteer die cel en kopieer deze. Selecteer nu alle nummers die u naar een getalnotatie wilt converteren en druk op Bewerken> Speciaal plakken. kiezen “Vermenigvuldigen” uit de sectie Bediening en druk op OK.

Alle nummers die u hebt geselecteerd, worden geconverteerd naar het algemene formaat, dat nummers herkent. Tekstcellen worden ook geconverteerd naar het algemene formaat, wat geen problemen zou veroorzaken.

Nummers of tekst extraheren uit cellen met een gemengde indeling

Nu komen we bij het moeilijke gedeelte: nummers ophalen uit cellen die meerdere invoerformaten bevatten. Als je een nummer en een eenheid hebt (zoals “7 schoppen,” zoals we hieronder hebben), zult u dit probleem tegenkomen. Om het op te lossen, gaan we een aantal verschillende manieren bekijken om cellen in getallen en tekst te splitsen, zodat je met elk afzonderlijk kunt werken. De eerste methode is een beetje omslachtig, maar werkt heel goed op kleine datasets.

LINKS / RECHTS & ZOEK

De hoofdfunctie die we hier zullen gebruiken is LINKS, waardoor de meest linkse tekens uit een cel worden geretourneerd. Zoals u in onze bovenstaande gegevensset kunt zien, hebben we cellen met nummers van één, twee en drie tekens, dus we moeten de meest linkse één, twee of drie tekens uit de cellen retourneren. Door LEFT te combineren met de functie ZOEKEN 4 Excel-lookup-functies om efficiënt spreadsheets te zoeken 4 Excel-lookup-functies om efficiënt spreadsheets te zoeken In een gigantisch Excel-spreadsheet kom je met CTRL + F pas tot nu toe. Wees slim en laat formules het harde werk doen. Opzoekformules besparen tijd en zijn gemakkelijk toe te passen. Meer lezen, we kunnen alles links van de ruimte retourneren. Dit is de functie:

= LINKS (A1, ZOEK ("", A1, 1))

Hiermee wordt alles links van de ruimte teruggezet. De vulgreep gebruiken Tijd besparen in Excel met behulp van de vulhendel Opslaan van tijd in Excel met behulp van de vulhendel Het hanteren van de vulhendel is de eerste stap om een ​​Excel-wizard te worden. Lees Meer om de formule toe te passen op de rest van de cellen, dit is wat we krijgen (je kunt de formule zien in de functiebalk bovenaan de afbeelding):

Zoals u kunt zien, hebben we nu alle nummers geïsoleerd, zodat ze kunnen worden gemanipuleerd. Wilt u de tekst ook isoleren? We kunnen de functie RECHTS op dezelfde manier gebruiken:

= RECHTS (A1, LEN (A1) -SEARCH ("", A1, 1))

Dit retourneert x tekens aan de rechterkant van de cel, waarbij x de totale lengte van de cel minus het aantal tekens links van de spatie is.

Nu kan de tekst ook worden gemanipuleerd. Wil je ze opnieuw combineren? Gebruik gewoon de CONCATENATE-functie met alle cellen als invoer:

= CONCATENATE (E1, F1)

Het is duidelijk dat deze methode het beste werkt als je alleen cijfers en eenheden hebt en verder niets. Als u andere celopmaken heeft, moet u misschien creatief worden met formules om alles naar behoren te laten werken. Als u een gigantische gegevensset hebt, is het de moeite waard de tijd te nemen die nodig is om de formule te bepalen!

Tekst naar kolommen

De functie Tekst naar kolommen is handig voor kolommen met alleen getallen, maar het kan ook het leven gemakkelijker maken als u cellen in cellen met een gemengd formaat hebt. Selecteer de kolom waarmee u wilt werken en druk op Gegevens> Tekst naar kolommen. U kunt dan de wizard gebruiken om een ​​scheidingsteken te selecteren (een spatie is meestal de beste) en uw kolom te laten splitsen zoals u het wilt.

Als u alleen cijfers van één en twee cijfers hebt, kan de optie Vaste breedte ook nuttig zijn, omdat deze alleen de eerste twee of drie tekens van de cel zal splitsen (u kunt een aantal splitsingen maken als u dat wilt, maar ik zal een volledige uitleg van Fixed Width-splitsing voor een ander artikel opslaan).

Als uw gegevensset veel kolommen bevat en u liever geen tekst naar kolommen voor elk van deze kolommen gebruikt, kunt u eenvoudig hetzelfde effect bereiken door snel te exporteren en importeren. Exporteer eerst uw spreadsheet als een CSV-bestand (comma-separated value). Klik Bestand> Opslaan als ... en sla uw bestand op als een CSV.

Open nu een nieuwe spreadsheet en klik Bestand> Importeren ... Selecteer uw CSV-bestand en gebruik de importwizard om uw gegevens op te splitsen in twee kolommen (u gebruikt exact dezelfde stappen als bij de wizard Tekst naar kolommen). Door selecteren “Ruimte” in het Delimiters-gedeelte vertel je Microsoft Excel om de gegevens te splitsen overal waar het een spatie vindt, wat de nummers en tekst isoleert.

Klik op Voltooien als u klaar bent en u krijgt een nieuwe spreadsheet waarin uw kolommen in tweeën worden gedeeld. Als u meer dan één spatie in een cel heeft, krijgt u natuurlijk meer dan twee kolommen, zoals u hier kunt zien:

Helaas is er geen goede oplossing hiervoor met deze methode; je moet alleen de cellen weer samenvoegen.

Meer gecompliceerde situaties

Met de bovenstaande strategieën moet u in staat zijn om getallen of tekst uit de meeste cellen met een gemengd formaat te halen die u problemen geven. Zelfs als dat niet het geval is, kunt u ze waarschijnlijk combineren met enkele van de krachtige tekstfuncties in Microsoft Excel om de tekens te krijgen die u zoekt. Er zijn echter een aantal veel gecompliceerdere situaties die om meer gecompliceerde oplossingen vragen.

Ik vond bijvoorbeeld een forumbericht waarin iemand de getallen uit een reeks wilde halen “45t * & 65 /”, zodat hij zou eindigen “4565.” Een andere poster gaf de volgende formule als een manier om het te doen:

= SOMPRODUCT (MIDDEN (0 & A1, LARGE (INDEX (ISNUMBER (- MIDDEN (A1, RIJ ($ 1: $ 25), 1)) * RIJ ($ 1: $ 25), 0), RIJ ($ 1: $ 25)) + 1, 1) * 10 ^ ROW ($ 1: $ 25) / 10)

Om helemaal eerlijk te zijn, heb ik geen idee hoe het werkt. Maar volgens de forumbericht, zal het de nummers uit een ingewikkelde reeks nummers en andere karakters halen. Het punt is dat je met genoeg tijd, geduld en moeite nummers en tekst uit bijna alles kunt halen! U hoeft alleen maar de juiste bronnen te vinden Hulp nodig met Excel-formules? 7 Bronnen om te raadplegen Heeft u hulp nodig met Excel-formules? 7 Bronnen om te raadplegen Excel is de gouden standaard voor spreadsheets. Als u Excel moet gebruiken en toch vertrouwd moet raken, zullen deze bronnen u snel kennis laten maken met de basis en meer. Lees verder .

Welke strategie gebruik je?

Nu u een beter idee hebt hoe u getallen en letters uit cellen in cellen met verschillende formaten kunt krijgen, willen we weten welke andere strategieën u hebt gebruikt om hetzelfde te doen. Plaats uw gekke Excel-formules, processen of iets anders dat nuttig kan zijn in de opmerkingen, zodat we allemaal van hen kunnen leren!

Ontdek meer over: Microsoft Excel, Spreadsheet.