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 på bilden nedan att cellerna i kolumn A lagras 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 som finns till det! 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 på 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 nummer som du vill konvertera till nummerformat och klicka på 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 problemet. 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 Hitta någonting i ditt Excel-kalkylblad med Lookup-funktioner Hitta någonting i ditt Excel-kalkylblad med Lookup-funktioner I ett jätte Excel-kalkylblad kommer CTRL + F bara att få dig så långt. 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 mellanslag.
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 jätte dataset kommer det att vara värt den tid det tar att få formeln räknat 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 tryck på 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 så som du vill ha den.
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). Klicka på Arkiv> Spara som ... och spara filen som en CSV.
Öppna nu ett nytt kalkylblad och klicka på Arkiv> Importera ... Välj din CSV-fil och använd importguiden för att få dina data uppdelade i två kolumner (du använder exakt samma steg som du gjorde med guiden Text till kolumner). Genom att välja "Space" i avsnittet Delimiters, berättar du i Microsoft Excel att dela upp data varhelst det finner 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 en enda väg 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 formulär, processer eller något annat som kan vara till hjälp i kommentarerna så att vi alla kan lära av dem!