Hur man extraherar ett nummer från en sträng i Excel

Hur

FörbiAdam Davidson

Publicerad7 juni 2023

Excel är ett kraftfullt kalkylblad, men det kan inte göra mirakel. Om du importerar data, och den informationen är en enda röra, kommer Excel inte att kunna göra mycket med den. Till exempel, om du har värden som är kopplade till andra tecken som en del av en sträng, kommer du inte att kunna utföra beräkningar på dem om du inte extraherar dem först.

Tack och lov är det möjligt att göra exakt det, även om det kan vara mer komplicerat än du först tror. Så här extraherar du ett nummer från en sträng i Excel.

Hur man extraherar ett nummer från slutet av en sträng med hjälp av Flash Fill

Ett snabbt och enkelt sätt att extrahera ett nummer från slutet av en sträng är att använda Excel Flash Fyll funktion. Med den här funktionen kan du snabbt fylla ett intervall av celler baserat på mönstret som upptäcks i en specifik cell. En nackdel med den här metoden är att den kräver att du gör den första extraheringen själv och om du bara vill extrahera ett nummer från en enda sträng kommer det inte att vara till någon nytta.

I de flesta fall kan Flash Fill extrahera numret korrekt från slutet av en sträng, även om det inte är ofelbart, så du bör alltid kontrollera resultaten noggrant. Om det inte fungerar korrekt, försök att manuellt fylla de första två eller tre cellerna istället för bara den första.

Så här extraherar du ett nummer från slutet av en sträng med Flash Fill:

1. Markera cellen bredvid den första cellen som innehåller dina strängar.
   
2. Ange numret som visas i slutet av strängen.
   
3. Om alla dina data finns i en kontinuerlig kolumn utan luckor, välj cellen direkt under den du precis skrev in numret i.
   
4. Om dina data har luckor i vissa rader, välj den första cellen och alla celler under som du vill extrahera siffror från, inklusive eventuella tomma rader.
   
5. Tryck Ctrl+E på Windows eller Cmd+E på Mac för att köra Flash Fill-funktionen.
6. Dina återstående celler kommer automatiskt att fyllas i baserat på mönstret som Excel har upptäckt. I de flesta fall kommer siffrorna från slutet av strängen att extraheras korrekt.
   

Hur man extraherar ett nummer från slutet av en sträng med hjälp av en formel

En nackdel med att använda Flash Fill-metoden är att den kräver att du extraherar siffror från strängen för den första raden själv. Det är möjligt att extrahera ett tal från slutet av en sträng med hjälp av en formel, även om formeln är ganska komplex.

Så här extraherar du ett tal från slutet av en sträng med en formel:

1. Klicka i cellen där du vill att det extraherade numret ska visas.
2. Ange följande formel och ersätt "B2" med cellen som innehåller din sträng:

   =VÄRDE(HÖGER(B2,LÅNG(B2)-MAX(OM(ÄRNUMMER(MIDDEL(B2,RAD(INDIREKT("1:"&LÅNG(B2))),1)*1)=FALSK, RAD(INDIREKT(" 1:"&LEN(B2))),0))))

3. Tryck Stiga på.
4. Numret ska extraheras från strängen.
5. För att tillämpa formeln på eventuella återstående celler, klicka på draghandtaget i det nedre högra hörnet av cellen.
   
6. Dra ner över cellerna där du vill att formeln ska replikeras.
   
7. Släpp musen och formeln kopieras till de markerade cellerna.
8. Dessa celler bör nu innehålla dina extraherade nummer.
   
9. Alla celler utan en sträng att extrahera från kommer att returnera ett fel. Om du istället vill returnera en tom cell kan du istället använda formeln:

   =FELFEL(VÄRDE(HÖGER(B2,LÄN(B2)-MAX(OM(ÄRNUMMER(MIDDEL(B2,RAD(INDIREKT("1:"&LÄN(B2))),1)*1)=FALSK, RAD(INDIREKT ("1:"&LEN(B2))),0)))),"")

10. Alla tomma celler kommer nu att returnera ett tomt utrymme.
    

Du kanske undrar vad denna formel gör. I korthet genererar den en array som är lika lång som strängen. Den placerar vart och ett av tecknen från strängen i denna array i ordning. Den utvärderar sedan var och en av dessa tecken för att se om de är ett nummer eller inte. Alla tecken som är siffror omvandlas till noll. Alla tecken som inte är siffror konverteras till ett tal som representerar dess position i strängen.

Till exempel skulle AB5HG23 konvertera till 1, 2, 0, 4, 5, 0, 0. Den letar sedan efter det högsta värdet i den här listan, vilket är positionen för det sista tecknet som inte är ett nummer. Slutligen returnerar den alla tecken efter denna punkt, som är siffrorna i slutet av strängen. Funktionen VÄRDE konverterar sedan tillbaka detta till ett tal. Enkelt, eller hur?

Hur man extraherar ett nummer från början av en sträng med hjälp av Flash Fill

Du kan också använda Flash Fill för att extrahera ett nummer från början av en sträng genom att själv fylla i det första exemplet och låta Excel stoppa mönstret.

Så här extraherar du ett nummer från början av en sträng med Flash Fill:

1. Välj cellen till höger om den första strängen som har ett nummer som du vill extrahera.
2. Ange numret som visas i början av strängen.
   
3. Om alla dina data finns i en kontinuerlig kolumn utan luckor, välj cellen direkt nedanför.
   
4. Om dina data har luckor markerar du den första cellen och alla celler under som du vill extrahera siffror från, inklusive eventuella tomma rader.
   
5. Tryck Ctrl+E på Windows eller Cmd+E på Mac för att starta Flash Fill.
6. Cellerna du valde ska nu visa de extraherade siffrorna.
   

Hur man extraherar ett nummer från början av en sträng med hjälp av en formel

Du kan också använda en formel för att extrahera ett tal från början av en sträng. Detta fungerar på ett liknande sätt som formeln i föregående avsnitt men hittar positionen för det första icke-numeriska tecknet och returnerar alla tecken före det.

Så här extraherar du ett tal från början av en sträng med en formel:

1. Välj cellen där du vill att det extraherade numret ska visas.
2. Ange följande formel och ersätt "B18" med cellen som innehåller din sträng:

   =VÄRDE(VÄNSTER(B18,MATCH(FALSK, ÄR NUMMER(MIDDEN(B18,RAD(INDIREKT("1:"&LÅNG(B18)+1)),1)*1),0)-1))

3. Tryck Stiga på och numret kommer att extraheras.
   
4. Du kan använd Autofyll i Excel för att kopiera formeln till alla andra celler du behöver.
5. Om du vill att några tomma rader ska returnera ett tomt resultat, använd följande formel istället:

   =FEL(VÄRDE(VÄNSTER(B18,MATCH(FALSK, ÄRNUMMER(MIDDEN(B18,RAD(INDIREKT("1:"&LÅNG(B18)+1)),1)*1),0)-1))," ")

Hur man extraherar nummer var som helst i en sträng med hjälp av Flash Fill

Som nämnts tidigare kanske Flash Fill inte fungerar om den första cellen inte matchar formatet för några av de andra cellerna. I det här fallet kan du behöva beräkna två eller tre celler manuellt innan Flash Fill ger dig det resultat du behöver.

Så här extraherar du nummer var som helst i en sträng med Flash Fill:

1. I cellen bredvid den första strängen skriver du manuellt siffrorna som finns i strängen.
   
2. Om alla dina data finns i en kontinuerlig kolumn, välj cellen direkt nedan.
   
3. Om dina data har några luckor, välj den första cellen och alla celler under som du vill extrahera siffror från, inklusive eventuella tomma rader.
   
4. Tryck Ctrl+E på Windows eller Cmd+E på Mac för att starta Flash Fill.
5. Cellerna ska nu fyllas med de extraherade siffrorna.
   

Hur man extraherar siffror från var som helst i en sträng med hjälp av en formel

Du kan använda en formel för att extrahera alla siffror från en sträng, oavsett var de visas.

Så här extraherar du tal var som helst i en sträng med en formel:

1. Välj cellen där du vill att det extraherade numret ska visas.
2. Ange följande formel och ersätt "B9" med cellen som innehåller din sträng:

   =SUMMAPRODUKT(MIDDEL(0&B9,STOR(INDEX(ÄRNUMMER(--MIDDA(B9,RAD(INDIREKT("1:"&LÅNG(B9))),1))*RAD(INDIREKT("1:"&LÅNG(B9) )),0),RAD(INDIREKT("1:"&LÅNG(B9))))+1,1)*10^RAD(INDIREKT("1:"&LÄN(B9)))/10)

3. Din formel bör extrahera alla siffror från strängen.
   
4. Om du vill att några tomma celler ska returnera en tom cell istället för noll, använd den här formeln istället:

   =FEL(SUMMAPRODUKT(MIDDEL(0&B9,STOR(INDEX(ÄRNUMMER(--MIDDA(B9,RAD(INDIREKT("1:"&LÄNGD(B9))),1))*RAD(INDIREKT("1:"&LÅNG( B9))),0),RAD(INDIREKT("1:"&LÄN(B9))))+1,1)*10^RAD(INDIREKT("1:"&LÄN(B9)))/10)," ")

5. Du kan använd Autofyll för att tillämpa formeln på andra celler i ditt kalkylark.
6. Alla strängar som inte innehåller några siffror kommer att returnera en nolla. Du kan lära dig hur konvertera en nolla till ett bindestreck i Excel om du vill ändra detta.

Utöka dina Excel-färdigheter

Att lära sig att extrahera ett nummer från en sträng i Excel kan hjälpa dig att snabbt extrahera den data du behöver från en lista med strängar. Även om formlerna som krävs kan vara komplexa, behöver du inte förstå exakt hur de fungerar; bara att de gör det!

Det finns fler användbara Excel-trick som du kan lära dig för att ta dina färdigheter till nästa nivå. Du kan lära dig hur man räknar celler med text i dem, eller hur beräkna någons ålder från deras födelsedatum. Du kan till och med skapa en slumptalsgenerator i Excel.