Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe nummer alleen uit tekststring in Excel te extraheren?


Alleen getallen uit tekstreeksen extraheren:

Doordat uw bureau Kutools for Excel's EXTRACTNUMBERS functie, kunt u snel alleen getallen uit de cellen van de tekstreeks halen.

doc haalt alleen nummers 14

Download en proef 60-dag gratis


Methode 1: haal nummer alleen uit tekststrings met formule


Met de volgende lange formule kunt u alleen de getallen uit de tekstreeksen extraheren, doe dit als volgt:

Selecteer een lege cel waar u het geëxtraheerde getal wilt uitvoeren en typ vervolgens deze formule: = SOMPRODUCT (MIDDEN (0 & A5, GROOT (INDEX (ISNUMBER (- MIDDEN (A5, RIJ (INDIRECT ("1:" & LEN (A5))), 1)) * RIJ (INDIRECT ("1:" & LEN (A5) )), 0), RIJ (INDIRECT ("1:" & LEN (A5)))) + 1, 1) * 10 ^ RIJ (INDIRECT ("1:" & LEN (A5))) / 10)en sleep vervolgens de vulgreep om het bereik te vullen dat u nodig hebt om deze formule toe te passen. Zie screenshot:

doc haalt alleen nummers 2

Opmerkingen:

  • 1. A5 staat de eerste gegevens die u alleen uit de lijst wilt halen.
  • 2. Het resultaat wordt getoond als 0 als er geen getallen in de reeks zijn.

Methode 2: haal nummer alleen uit tekststrings met VBA-code

Hier is een VBA-code die u ook een plezier kan doen, doe als volgt:

1. Houd de toets ingedrukt Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: haal nummer alleen uit een tekststring:

Sub ExtrNumbersFromRange()
    Dim xRg As Range
    Dim xDRg As Range
    Dim xRRg As Range
    Dim nCellLength As Integer
    Dim xNumber As Integer
    Dim strNumber As String
    Dim xTitleId As String
    Dim xI As Integer
    xTitleId = "KutoolsforExcel"
    Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
    If TypeName(xDRg) = "Nothing" Then Exit Sub
    Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
    If TypeName(xRRg) = "Nothing" Then Exit Sub
    xI = 0
    strNumber = ""
  For Each xRg In xDRg
    xI = xI + 1
    nCellLength = Len(xRg)
    For xNumber = 1 To nCellLength
      If IsNumeric(Mid(xRg, xNumber, 1)) Then
        strNumber = strNumber & Mid(xRg, xNumber, 1)
      End If
    Next xNumber
    xRRg.Item(xI) = strNumber
    strNumber = ""
  Next xRg
End Sub

3. En druk vervolgens op F5 toets om deze code uit te voeren, en een promptvakje wordt uitgeknipt om te herinneren aan het selecteren van het tekstbereik dat u wilt gebruiken, zie screenshot:

doc haalt alleen nummers 3

4. Dan klikken OK, nog een prompt volgt, selecteer een cel om het resultaat uit te voeren, zie screenshot:

doc haalt alleen nummers 4

5. Klik ten slotte op OK knop, en alle getallen in de geselecteerde cellen zijn in één keer geëxtraheerd.


Methode 3: haal nummer alleen uit tekststring met Kutools voor Excel

Kutools for Excel heeft ook een krachtige functie die wordt genoemd EXTRACTNUMBERS, met deze functie kunt u snel alleen de getallen uit de oorspronkelijke tekstreeksen extraheren.

Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis om zonder beperking te proberen in 60-dagen.

Na het installeren van Kutools for Excel, alsjeblieft als volgt doen:

1. Klik op een cel naast uw tekstreeks waar u het resultaat plaatst, zie screenshot:

doc haalt alleen nummers 5

2. Dan klikken Kutools > Kutools-functies > Tekst > EXTRACTNUMBERSzie screenshot:

doc haalt alleen nummers 6

3. In de Functieargumenten dialoogvenster, selecteert u een cel waarvan u de nummers wilt extraheren uit de Tekst tekstvak en voer vervolgens in waar or vals in de N tekstvak, zie screenshot:

doc haalt alleen nummers 7

Notes: het argument N is een optioneel item, als u invoert waar, het zal de getallen als numeriek terugsturen, als u invoert vals, het zal de getallen als tekstindeling retourneren, de standaard is onwaar, dus u kunt het leeg laten.

4. En klik vervolgens op OK, de getallen zijn geëxtraheerd uit de geselecteerde cel en sleep de vulgreep naar de cellen die u deze functie wilt toepassen, u krijgt het volgende resultaat:

doc haalt alleen nummers 8

Klik om te downloaden en proef Kutools nu voor Excel!


Methode 4: teksttekenreeks afzonderlijk in tekst- en getalkolommen verdelen met Kutools voor Excel

Als u de tekenreeks in afzonderlijke tekst- en nummerkolommen wilt splitsen, Kutools for Excel's Gespleten cellen kan u ook helpen deze taak op te lossen.

Na het installeren van Kutools for Excel , alsjeblieft als volgt doen:

1. Selecteer de tekstreeks die u wilt splitsen en klik vervolgens op Kutools > Tekst > Gespleten cellenzie screenshot:

doc haalt alleen nummers 9

2. In de Gespleten cellen dialoogvenster, selecteer Splitsen naar kolommen onder de Type sectie en controleer vervolgens Tekst en nummer van de Splitsen door sectie, zie screenshot:

doc haalt alleen nummers 10

3. En klik vervolgens op Ok knop, selecteer een cel om het resultaat in het uitgeklapte dialoogvenster te plaatsen, zie screenshot:

doc haalt alleen nummers 11

4. Dan klikken OK knop en de tekstreeksen zijn gesplitst in afzonderlijke tekst- en nummerkolommen als volgt:

doc haalt alleen nummers 12

Klik om te downloaden en proef Kutools nu voor Excel!


Methode 5: Extract decimale nummer alleen uit tekststring met formule

Als de tekenreeksen met enkele decimale getallen in uw werkblad alleen de decimale getallen uit de tekenreeksen kunnen extraheren?

Met de onderstaande formule kunt u de decimale getallen snel en eenvoudig uit de tekstreeksen extraheren.

Voer deze formule in:=LOOKUP(9.9E+307,--LEFT(MID(A5,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A5&"1023456789")),999),ROW(INDIRECT("1:999"))))En vul vervolgens de hendel in tot de cellen die u deze formule wilt laten bevatten, alle decimale getallen zijn geëxtraheerd uit de tekstreeksen, zie screenshot:

doc haalt alleen nummers 13


Extract nummer alleen van strings met Kutools voor Excel

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Relatieve artikelen:


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Werkt met Office 2007-2019 en 365 en ondersteunt alle talen. Het is eenvoudig te implementeren in uw bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Veras · 27 days ago
    Tkssssss you saved my day :) God bless you
  • To post as a guest, your comment is unpublished.
    Alex · 2 months ago
    Hi ! You guys are impressive with formulas... I need one to extract the room number of a conference room. The Source Cells looks like AB ABCD CITY-Street-1-004-REST/Pers1 ABCD AB. I need to extract the 004 from it. The city lenght varies, as well as the street name as well as the floor number (this one is 1 but it could be 12). I was trying to find a way to jump to third "-" and take only the follow digits and stop at next alphabetical characters. I don't want to take the numbers of persons the room can have (Pers1)...

    Much appreciated guys!!!!
  • To post as a guest, your comment is unpublished.
    Aaron Freije · 2 months ago
    Hello, I could use help with a formula. I have read through these posts thinking I could find a formula or two that I could pull from to make work but I can't get it just right. I have product descriptions where the last section represents the case count (i.e CS/6 means 6 in a case). I would like a formula to pull out just the case count number. Here are a couple examples: LUCR LACT FR 1 % CALC CHOC 64 OZ CS/6 (need 6 pulled out), PCHP DAIRY CRMR FR VAN 32 OZ CS/12 (need 12 pulled out), and GLEN HALFNHALF ASEP SS GU 3/8 OZ CS/360 (need 360 pulled out).
  • To post as a guest, your comment is unpublished.
    Satya · 3 months ago
    4900 Meridian Street, Normal, Alabama 35762
    445 Health Sciences Boulevard, Dothan, Alabama 36303-2251

    how to separate the State code number which is presented in last
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hi, Satya,
      To extract only the state code number, you should apply the below formula:
      =MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,5)
      If you want to extract all numbers in the last, please use this formula:
      =MID(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,10)
  • To post as a guest, your comment is unpublished.
    Shahid · 6 months ago
    This isn't working for decimals, it's removing decimal point.
  • To post as a guest, your comment is unpublished.
    josia · 10 months ago
    for example if i have these two strings, how do i extract the numbers that begin with 44133 in cells B1 and B2

    cell A1: "255 water bill payment 44133256487 payment by 255766854254 dsm"

    Cell A2: "255 payment by 255745654875 dsm water bill 44133647851"
    • To post as a guest, your comment is unpublished.
      MOHAMMAD OWAIS · 28 days ago
      first do a find. =FIND(44,A1)
      then mid =MID(A1,FIND(44,A1),11)

      I wish it is helpful.
  • To post as a guest, your comment is unpublished.
    josia · 10 months ago
    hi.if i have a list which has text and Control numbers and phone number all in one cell but each record has different positions for the 3, and i want to extract control numbers(which all begin with say value 44133*****) how best can i extract that information
  • To post as a guest, your comment is unpublished.
    Farhad · 10 months ago
    Hi
    Can any one help me to solve this problem :


    " Hotel to kadamtali: 20/(r).kadamtali to charkhai bazar:90/.(cng).bazar to site:20/(r).site to charkhai bazar:20/.charkhai bazar to sharker bazar:80/.(cng) kanaigath to site:30/(r).site to kanaigath:50/(r).carry matarials).sharkerbazar to syleth : 870/.(cng Reserve,carry one site matarials).kadamtali to zindabazar:40/.zindbazar to amborkhana:20/.amborkhana to companygonj:200/.(cng).companygonj to vholagonj site:30/.vholagonj site to amborkhana:2 seat,400/cng.(carry matarials),amborkhana to upashar:60/(cng).carry matarials.



    Here i want to take the value ( "/") before this. and add them.
  • To post as a guest, your comment is unpublished.
    KA · 1 years ago
    Here's a nasty one I'm stuck on - I want to separately extract both numbers from this cell: Down(+): 3,537.78 Over(+): 1,965.30

    Each number can vary in length, but will always have two digits after the decimal.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, KA,
      May be the Text to Columns feature in Excel can help you to separate the text and numbers, please see the following screenshot:

      After splitting the cell contents, you just need to delete the text columns and only keep the number columns as you need.
  • To post as a guest, your comment is unpublished.
    Excel Master · 1 years ago
    Thank you. Its great.
  • To post as a guest, your comment is unpublished.
    Matt · 1 years ago
    "Select a blank cell that is adjacent to the list you want to extract number only, and type this formula =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10) (A2 stands the first data you want to extract numbers only from the list), then press Shift + Ctrl + Enter buttons, and drag the fill handle to fill the range you need to apply this formula"

    It's not necessary to press Shift + Ctrl + Enter. Press only ENTER (sumproduct know to work with array)
    • To post as a guest, your comment is unpublished.
      Melvin Koshy · 1 years ago
      This is absolutely amazing.
      1. Please explain the logic of this formula briefly
      2. Could you explain why we have to press Ctrl+Shift+Enter.
  • To post as a guest, your comment is unpublished.
    andrew · 1 years ago
    Hi,
    i want to extract only the number after the Colon in a cell
    80lb : 12
    90lb : 4
    110lb : 0
    120lb : 20
    130lb : 6
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, andrew,
      To extract the numbers after the colon,pleas apply the below formula:
      =MID(A1,FIND(":",A1)+2,256)
      Please try it, thank you!
  • To post as a guest, your comment is unpublished.
    Rashmita · 1 years ago
    I want to extract pincodes from this list:

    Adikavi Nannaya University, Jaya Krishnapuram, Rajahmundry – 533 105, Andhra Pradesh.
    Andhra University, Visakhapatnam-530 003.
    Acharya Nagarjuna University, Nagarjuna Nagar, Guntur-522 510.
    Dravidian University, Kuppam-517 425.

    Is there anyone can help me on this?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Rashmita,

      If you just need to extract the number codes from the addresses, may be the Kutools for Excel's Extractnumbers function can help you.
      Please view the screenshot image:
  • To post as a guest, your comment is unpublished.
    vikram · 2 years ago
    How do i get True or false when i compare numbers & text for example
    d1, d2, d3 3 TRUE


    d1, d2, d4, d5 5 FALSE
  • To post as a guest, your comment is unpublished.
    Dexter · 2 years ago
    Hi,
    I would like to know if there is a formula to get the total $ value of this string in Excel (Guy1-$201.6, Guy2-$915.85, Guy3-$495, Guy4-$1211, Guy5-$492) - and extent to 20 'Guys'. I don't want to convert etc ... I want a formula please.
    Thanks
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      Try the formula

      =MID(F14,FIND("$",F14),100)+0
  • To post as a guest, your comment is unpublished.
    Samoil · 2 years ago
    Updated Formula which handles decimal point and works on any number length

    =SUMPRODUCT(MID(0&D2,LARGE(INDEX(ISNUMBER(--MID(D2,ROW(INDIRECT("$1:$"&LEN(D2))),1))*
    ROW(INDIRECT("$1:$"&LEN(D2))),0),ROW(INDIRECT("$1:$"&LEN(D2))))+1,1)*10^ROW(INDIRECT("$1:$"&LEN(D2)))/POWER(10, IFERROR(LEN(D2)-FIND(".", D2)-1, 1)))

    D2 is the cell where you have number in a string format
    Copy this formula to E2 and press CTRL+SHIFT+ENTER
    • To post as a guest, your comment is unpublished.
      umari · 1 years ago
      it doesnt work on below scenario:

      Provident<space>Fund<2spaces>-5.55556%<space>0.00<space>

      Same like this : Provident Fund -5.55556% 0.00
      i want -5.55556% or -0.055556
    • To post as a guest, your comment is unpublished.
      Noeun · 2 years ago
      hi man can you explain how the formula works?
  • To post as a guest, your comment is unpublished.
    adrie farndell · 3 years ago
    Good day
    I would like to extract the first set of numbers from a list. ie (122,90,84,118.4,128.9)
    Any ideas on what formula I can use?
    COIL112X2.5
    COIL90X2.5
    COIL84X2.0
    COIL118.4X1.8
    COIL128.9X2.0
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      =+MID(C14,5,FIND("X",C14)-1-LEN("coil"))
    • To post as a guest, your comment is unpublished.
      Giri · 2 years ago
      Try the formula
      =+MID(C13,5,FIND("X",C13)-1-LEN("coil"))
  • To post as a guest, your comment is unpublished.
    Yusuf Ali · 3 years ago
    I have a huge list of Barcodes with product description which might have weights and other numeric values. I wish to extract only the barcodes which are 8-13 digits in len. Is there any way the above mentioned formulae can be tweeked.
  • To post as a guest, your comment is unpublished.
    adeel · 3 years ago
    thanks, really helpful
  • To post as a guest, your comment is unpublished.
    Deepak · 3 years ago
    I want the Formula that contains number but stop working where next alphabet is start.

    Like: hh123456f2
    gh123f3
    ff1234g1

    I don't want last numerical number after the alphabet.

    Hope you understand what I'm trying
  • To post as a guest, your comment is unpublished.
    salar lotfee · 4 years ago
    perfect, just have wasted a day in the factory by the scanning machine in 38' centigrade standing with no results, but this blew it up. super thanks
  • To post as a guest, your comment is unpublished.
    mohamed elrify · 4 years ago
    I think that this formula will be easier
    =SUM(IFERROR(VALUE(MID(A1;ROW($1:$100);1));0))
    • To post as a guest, your comment is unpublished.
      Deepak Kumar · 4 years ago
      [quote name="mohamed elrify"]I think that this formula will be easier
      =SUM(IFERROR(VALUE(MID(A1;ROW($1:$100);1));0))[/quote]

      What u have posted will some then not extract them!!
  • To post as a guest, your comment is unpublished.
    dEEPAK · 4 years ago
    This will more short..

    =NPV(-0.9,IFERROR(MID(A1,100-ROW($1:$99),1)/10,""))

    with CSE
    • To post as a guest, your comment is unpublished.
      Gopal · 2 years ago
      Great! this works Deepak!

      only one problem - it won't work with the decimals. example, if A1 is 2.25LXX, it will extract it as 225, not 2.25, is there a way in your formula to extract the decimal number also? thanks in advance!
  • To post as a guest, your comment is unpublished.
    dEEPAK · 4 years ago
    How about this... :-)

    [b]=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW($1:$999),1)/10,""))
    [/b]
    [i]Press Ctrl + Shift + Enter[/i]
  • To post as a guest, your comment is unpublished.
    xlViki · 4 years ago
    @jb: Use this UDF:


    Function ExtractNumber(cell As Range) As Long
    Dim Num As String

    For i = 1 To Len(cell)
    If IsNumeric(Mid(cell.Value, i, 1)) Then Num = Num & Mid(cell.Value, i, 1)
    Next i

    ExtractNumber = Num

    End Function
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    this formula
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

    worked great, for any number under 4 digital but I have numbers over 1000 that I need to extract - any advice??

    thanks :)
  • To post as a guest, your comment is unpublished.
    jb · 4 years ago
    Thank you thank you!

    have been trolling the web for answer to simply extra numbers from string in excel and after many overbearing, confusing formulas yours worked like a treat!!

    much appreciated :D
  • To post as a guest, your comment is unpublished.
    Georgios · 4 years ago
    Great formula...very helpful!
    But, could you please advise what can I do with chinese characters? I guess it considers them as figures. Any idea?
  • To post as a guest, your comment is unpublished.
    Muksharna G · 4 years ago
    This formula fails with decimal places. e.g. "1.5 grams of abc" returns "15".
  • To post as a guest, your comment is unpublished.
    Allen Kelly · 4 years ago
    Christ it was either [b]Multiplan or Quattro Pro that used to[/b] return the string value of a single item with a single command...
  • To post as a guest, your comment is unpublished.
    Mandeep · 5 years ago
    Who can explain this formula to me .