Så här extraherar du ett nummer eller en text från Excel med den här funktionen

Microsoft Excel är bra att arbeta med både siffror och text Spara tid med textoperationer i Excel Spara tid med textoperationer i Excel Excel kan göra magi med siffror och det kan hantera tecken lika bra. Den här handboken visar hur man analyserar, konverterar, ersätter och redigerar text i kalkylblad. Dessa grunder gör att du kan utföra komplexa omvandlingar. Läs mer-men om du använder båda i samma cell kanske du stöter på lite svårigheter. Lyckligtvis kan du extrahera nummer eller text från celler för att arbeta med dina data mer effektivt. Vi visar flera alternativ, beroende på det format som dina data är närvarande i.

Numrer lagrade som text

Detta är en vanlig situation, och lyckligtvis, mycket lätt att hantera. Ibland är celler som innehåller endast siffror felaktigt märkta som text, vilket förhindrar att Microsoft Excel använder dem i operationer. Du kan se i bilden nedan att cellerna i kolumn A är lagrade som text, som anges av den gröna flaggan på varje cell samt formatfältet, som båda är markerade nedan.

Konvertera till nummer

För att lösa problemet, klicka bara på en cell, klicka på varningsrutan och välj “Konvertera till nummer.” Det är allt det finns! Detta kan göras på flera celler genom att välja dem alla och klicka på varningsrutan, men det är troligtvis inte lika effektivt som följande metod.

Text till kolumner

Om du har många celler som behöver åtgärdas, kan det hända att den här metoden använder en monumental tid. För att komma igenom processen snabbare kan du använda Microsoft Excels text till kolumner Så här konverterar du avgränsade textfiler till Excel-kalkylblad Så här konverterar du avgränsade textfiler till Excel-kalkylblad Använd Microsoft Excel för att importera data från en textfil till ett kalkylblad. Guiden Textimport hjälper dig att importera data från avgränsad text och ordna det ordentligt. Läs mer funktion. Välj de nummer som du vill konvertera, gå till Data> Text till kolumner, och använd guiden för att se till att siffrorna kommer ut korrekt (för det mesta behöver du bara klicka Nästa och Avsluta utan att röra med någon av inställningarna).

Texten konverteras till nummer och du är klar att gå. Observera att detta bara fungerar i en kolumn åt gången, så om du har många kolumner kanske du vill gå med den här sista metoden.

Klistra in special

För att den här metoden ska fungera måste du ange nummer 1 i en cell (det är viktigt att det här är i nummerformat). Välj den cellen och kopiera den. Välj nu alla de nummer som du vill konvertera till nummerformat och träffa Redigera> Klistra in special. Välj “Multiplicera” från avsnittet Operation och tryck OK.

Alla de siffror du valt kommer att konverteras till det allmänna formatet, vilket känner igen tal. Alla textceller kommer också att konverteras till det allmänna formatet, vilket inte skulle orsaka några problem.

Extracting Numbers eller Text från Mixed Format Cells

Nu kommer vi till den svåra delen: att få siffror ur celler som innehåller flera formater av inmatning. Om du har ett nummer och en enhet (som “7 skovlar,” som vi har nedan), kommer du att lösa detta problem. För att lösa det ska vi titta på ett par olika sätt att dela upp celler i siffror och text, så att du kan arbeta med var och en för sig. Den första metoden är lite besvärlig men fungerar väldigt bra på små dataset.

VÄNSTER / HÖGER & SÖK

Huvudfunktionen som vi använder här är VÄNSTER, som returnerar de vänstra tecknen från en cell. Som du kan se i vårt dataset ovan har vi celler med en-, två- och treteckningsnummer, så vi måste returnera den vänstra en-, två- eller tre tecken från cellerna. Genom att kombinera VÄNSTER med SEARCH-funktionen 4 Excel Lookup-funktioner för att effektivt söka kalkylblad 4 Excel Lookup-funktioner för att effektivt söka kalkylblad I ett jätte Excel-kalkylblad kommer CTRL + F bara att få dig hittills. Var smart och låt formler göra det hårda arbetet. Lookup formler sparar tid och är lätta att tillämpa. Läs mer, vi kan returnera allt till vänster om utrymmet. Här är funktionen:

= VÄNSTER (A1, SEARCH ("", A1, 1))

Detta kommer att returnera allt till vänster om utrymmet. Använda fyllnadshandtaget Så här sparar du tid i Excel genom att använda Fill Handle Hur man sparar tid i Excel genom att använda Fill Handle Mastering Fill Handle är det första steget att bli en Excel-guide. Läs mer om du vill använda formeln till resten av cellerna, det här är vad vi får (du kan se formeln i funktionsfältet längst upp på bilden):

Som ni kan se har vi nu alla numren isolerade, så de kan manipuleras. Vill du också isolera texten? Vi kan använda RIGHT-funktionen på samma sätt:

= Höger (A1, LEN (A1) -SEARCH ("", A1, 1))

Detta returnerar x tecken från cellens högra sida, där x är cellens totala längd minus antalet tecken till vänster om utrymmet.

Nu kan texten också manipuleras. Vill du kombinera dem igen? Använd bara CONCATENATE-funktionen med alla celler som ingångar:

= CONCATENATE (E1, F1)

Självklart fungerar den här metoden bäst om du bara har nummer och enheter, och inget annat. Om du har andra cellformat kanske du måste bli kreativ med formler för att få allt att fungera rätt. Om du har en gigantisk dataset kommer det att vara värt den tid det tar att få formeln att räknas ut!

Text till kolumner

Funktionen Text till kolumner är användbar för kolumner med enbart tal, men det kan också göra livet enklare om du har blandade formatceller. Markera kolumnen som du vill arbeta med och träffa Data> Text till kolumner. Du kan sedan använda guiden för att välja en avgränsare (ett utrymme är vanligtvis det bästa) och har din kolumn delad på det sätt du vill ha det.

Om du bara har en- och tvåsiffriga siffror kan alternativet Fast bredd också vara användbart eftersom det bara delar upp de två första eller tre tecknen i cellen (du kan skapa ett antal splittringar om du vill, men jag sparar en fullständig förklaring av Fixed Width splitting för en annan artikel).

Om din dataset innehåller många kolumner, och du hellre inte vill använda Text till kolumner för var och en av dem, kan du enkelt få samma effekt med snabb export och import. Först exporterar du kalkylbladet som en CSV-fil (Comma Separated Value). Klick Fil> Spara som ... och spara din fil som en CSV.

Nu öppnar du ett nytt kalkylblad och klickar på Arkiv> Importera ... Välj din CSV-fil och använd importguiden för att få dina data delade i två kolumner (du använder exakt samma steg som du gjorde med guiden Text till kolumner). Genom att välja “Rymden” I avsnittet Delimiters kommer du att berätta för Microsoft Excel att dela upp uppgifterna varhelst de hittar ett mellanslag, vilket kommer att isolera siffrorna och texten.

Klicka på Slutför när du är klar, och du får ett nytt kalkylblad som har dina kolumner uppdelade i två. Naturligtvis, om du har mer än ett utrymme i en cell, kommer du att sluta med mer än två kolumner, som du kan se här:

Tyvärr finns det ingen bra lösning för detta med hjälp av denna metod. du måste bara sammanfoga cellerna tillsammans igen.

Mer komplicerade situationer

Med strategierna ovan borde du kunna extrahera nummer eller text ur de flesta blandade celler som ger dig problem. Även om de inte gör det kan du förmodligen kombinera dem med några av de kraftfulla textfunktionerna som ingår i Microsoft Excel för att få de tecken du letar efter. Det finns emellertid några mycket mer komplicerade situationer som kräver mer komplicerade lösningar.

Till exempel hittade jag ett foruminlägg där någon ville extrahera numren från en sträng som “45t * & 65 /”, så att han skulle sluta med “4565.” En annan affisch gav följande formel som ett sätt att göra det:

= SUMPRODUKT (MID (0 och A1, LARGE (INDEX (ISNUMBER (- MID (A1, RÅD ($ 1: $ 25), 1)) * RÅD ($ 1: $ 25), 0) 1) * 10 ^ RAD ($ 1: $ 25) / 10)

För att vara helt ärlig har jag ingen aning om hur det fungerar. Men enligt forumposten kommer det att ta siffrorna ut ur en komplicerad sträng av siffror och andra tecken. Poängen är att med tillräckligt med tid, tålamod och ansträngning kan du extrahera siffror och text från nästan allt! Du behöver bara hitta rätt resurser Behöver du hjälp med Excel-formulär? 7 Resurser att konsultera Behöver du hjälp med Excel-formulär? 7 Resurser att konsultera Excel är kalkylbladets guldstandard. Om du är skyldig att använda Excel och ändå måste bekanta dig med det, kommer dessa resurser snabbt att presentera dig för grunderna och mer. Läs mer .

Vilken strategi använder du?

Nu när du har en bättre uppfattning om hur man får siffror och bokstäver ur blandade formatceller vill vi veta vilka andra strategier du har använt för att göra samma sak. Skicka in dina galna Excel-formulär, processer eller något annat som kan vara till hjälp i kommentarerna så att vi alla kan lära av dem!

Utforska mer om: Microsoft Excel, kalkylblad.