Hur man extraherar ett nummer eller text från Excel (2023)

Microsoft excelär bra på att arbeta med både siffror och text --- men om du använder båda i samma cell kan du stöta på vissa svårigheter. Lyckligtvis kan du extrahera siffror eller text från celler för att arbeta med dina data mer effektivt. Vi visar flera alternativ, beroende på vilket format din data för närvarande är i.

Excel-nummer formaterade som text

Detta är en vanlig situation, och---lyckligtvis---mycket lätt att hantera. Ibland är celler som bara innehåller siffror felaktigt märkta eller formaterade som text, vilket hindrar Microsoft Excel från att använda dem i operationer.

Du kan se på bilden nedan att cellerna i kolumn A är formaterade som text, vilket indikeras av rutan för nummerformat. Du kan också se en grön flagga i det övre vänstra hörnet av varje cell.

Konvertera text till nummer i Excel

Om du ser den gröna flaggan i det övre vänstra hörnet, välj en eller flera celler, klicka på varningsskylten och väljKonvertera till nummer.

Annars markerar du cellerna och väljer standard på menyn Talformat i menyfliksområdetsiffraalternativ.

Om du behöver mer detaljerade alternativ högerklickar du på de markerade cellen/cellerna och väljerFormatera celler, som öppnar respektive meny. Här kan du anpassa talformatet och lägga till eller ta bort decimaler, lägga till en 1 000-avgränsare eller hantera negativa tal.

Uppenbarligen kan du också använda alternativen för band eller formatera celler som beskrivs ovan för att konvertera ett tal till text, eller text till valuta, tid eller något annat format du önskar.

Använd nummerformatering med Excels Paste Special

För att den här metoden ska fungera måste du ange ett nummer (vilket som helst) i en cell; det är viktigt att den här cellen också är formaterad som ett tal. Kopiera den cellen. Välj nu alla celler som du vill konvertera till talformatet, gå tillHem > Klistra in > Klistra in special, VäljFormatför att bara klistra in formateringen av cellen du kopierade från början, klicka sedanOK.

Denna operation tillämpar formatet för cellen du kopierade på alla markerade celler, även textceller.

Extrahera siffror eller text från celler med blandat format

Nu kommer vi till den svåra delen: få ut siffror från celler som innehåller flera inmatningsformat. Om du har ett nummer och en enhet (som "7 spadar", som vi har nedan), kommer du att stöta på det här problemet. För att lösa det kommer vi att titta på ett par olika sätt att dela upp celler i siffror och text, så att du kan arbeta med varje individuellt.

Separera siffror från text

Om du har många celler som innehåller en blandning av siffror och text eller multiplar av båda, kan det ta en enorm tid att separera dem manuellt. För att ta dig igenom processen snabbare kan du använda Microsoft ExcelText till kolumnerfungera.

Välj de celler som du vill konvertera, gå tillData > Text till kolumner, och använd guiden för att se till att cellerna kommer ut korrekt. För det mesta behöver du bara klickaNästaochAvsluta, men se till att du väljer en matchande avgränsare; i det här exemplet ett kommatecken.

Om du bara har en- och tvåsiffriga nummer,Fast breddalternativet kan också vara användbart, eftersom det bara delar av de första två eller tre tecknen i cellen. Du kan till och med skapa ett antal uppdelningar på det sättet.

Notera:Celler formaterade som text kommerinteautomatiskt framträda med en talformatering (eller vice versa), vilket innebär att du kanske fortfarande måste konvertera dessa celler enligt beskrivningen ovan.

Extrahera ett nummer eller en text från en avgränsad sträng

Denna metod är lite krånglig, men fungerar mycket bra på små dataset. Vad vi antar här är att ett mellanslag skiljer numret och texten åt, även om metoden även fungerar för alla andra avgränsare.

Huvudfunktionen vi kommer att använda här är LEFT, som returnerar tecknen längst till vänster från en cell. Som du kan se i vår datauppsättning ovan, har vi celler med ett-, två- och treteckensnummer, så vi måste returnera ett, två eller tre tecken längst till vänster från cellerna. Genom att kombinera VÄNSTER medSÖK funktion, vi kan lämna tillbaka allt till vänster om utrymmet. Här är funktionen:

=VÄNSTER(A1, SÖK(" ", A1, 1))

Detta kommer att returnera allt till vänster om utrymmet. Genom att använda fyllningshandtaget för att tillämpa formeln på resten av cellerna, det här är vad vi får (du kan se formeln i funktionsfältet överst i bilden):

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

=HÖGER(A1, LEN(A1)-SÖK(" ", A1, 1))

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

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

=KONKATERA(E1; F1)

Uppenbarligen fungerar den här metoden bäst om du bara har siffror och enheter, och inget annat. Om du har andra cellformat måste du kanske vara kreativ med formler för att få allt att fungera. Om du har en gigantisk datauppsättning kommer det att vara värt tiden det tar att få reda på formeln!

Extrahera ett nummer från ena änden av en kontinuerlig sträng

Vad händer nu om det inte finns någon avgränsare som skiljer ditt nummer och text åt?

Om du ärextrahera numret från vänster eller höger om strängen, kan du använda en variant av VÄNSTER- eller HÖGER-formeln som diskuteras ovan:

=VÄNSTER(A1,SUMMA(LÅNG(A1)-LÄNGD(ERSÄTTNING(A1;{"0","1","2","3","4","5","6","7" "8","9"},""))))

=HÖGER(A1,SUMMA(LÅNG(A1)-LÄNGD(ERSÄTTNING(A1;{"0","1","2","3","4","5","6","7" "8","9"},""))))

Detta kommer att returnera alla nummer från vänster eller höger om strängen.

Om du ärextrahera numret från höger om strängen, kan du också använda en tvåstegsprocess. Bestäm först platsen för din första siffra i strängen med hjälp av MIN-funktionen. Sedan kan du mata in den informationen i en variant av RÄTT formel för att dela upp dina siffror från dina texter.

=MIN(SÖK({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

=HÖGER(A1, ENDAST(A1)-B1+1)

Notera:När du använder dessa formler, kom ihåg att du kanske måste justera kolumntecknen och cellnumren.

Extrahera nummer från båda ändarna av en kontinuerlig sträng

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

Till exempel hittade jag enforuminläggdär någon ville extrahera siffrorna 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:

=SUMMAPRODUKT(MIDDA(0&A1,STORT(INDEX(ÄRNUMMER(--MIDDA(A1,RAD($1:$25),1))*
RAD($1:$25),0),RAD($1:$25))+1,1)*10^RAD($1:$25)/10)

För att vara helt ärlig har jag ingen aning om hur det fungerar. Men enligt foruminlägget kommer det att ta siffrorna 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 vad som helst! Du måste barahitta rätt resurser.

Efter några fler Excel-tips? Här ärhur man kopierar formler i Excel.