Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
S'inscrire  \/ 
x

or

Comment extraire le numéro uniquement de la chaîne de texte dans Excel?


Méthode 1: Extraire le numéro uniquement des chaînes de texte avec formule

La formule longue suivante peut vous aider à extraire uniquement les nombres des chaînes de texte, procédez comme suit:

Sélectionnez une cellule vide dans laquelle vous souhaitez afficher le nombre extrait, puis tapez cette formule: = SUMPRODUCT (MID (0 & A5, LARGE (INDEX (ISNUMBER (- MID (A5, ROW (INDIRECT ("1:" & LEN (A5))), 1)) * ROW (INDIRECT ("1:" & LEN (A5) )), 0), ROW (INDIRECT ("1:" & LEN (A5)))) + 1, 1) * 10 ^ ROW (INDIRECT ("1:" & LEN (A5))) / 10), puis faites glisser la poignée de recopie pour remplir la plage dont vous avez besoin pour appliquer cette formule. Voir la capture d'écran:

doc extrait les numéros seulement 2

Notes:

  • 1. A5 représente les premières données que vous souhaitez extraire uniquement de la liste.
  • 2. Le résultat sera affiché comme 0 lorsqu'il n'y a pas de nombres dans la chaîne.

Extraire les nombres uniquement des chaînes de texte:

Avec Kutools pour Excel's NOMBRE D'EXTRAITS fonction, vous pouvez extraire rapidement uniquement des nombres des cellules de la chaîne de texte. Cliquez pour télécharger Kutools for Excel!

doc extrait les numéros seulement 14


Méthode 2: extraire le numéro uniquement des chaînes de texte avec le code VBA

Voici un code VBA qui peut également vous rendre service, procédez comme suit:

1. Maintenez le Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > Moduleet collez le code suivant dans le Module Fenêtre.

Code VBA: extraire le numéro uniquement de la chaîne de texte:

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. Et puis, appuyez sur F5 pour exécuter ce code, et une boîte de dialogue apparaît pour vous rappeler de sélectionner la plage de texte que vous souhaitez utiliser, voir capture d'écran:

doc extrait les numéros seulement 3

4. Puis clique OK, une autre boîte de dialogue suit, veuillez sélectionner une cellule pour afficher le résultat, voir capture d'écran:

doc extrait les numéros seulement 4

5. Enfin, cliquez OK bouton, et tous les nombres des cellules sélectionnées ont été extraits en même temps.


Méthode 3: Extraire le numéro uniquement de la chaîne de texte avec Kutools for Excel

Kutools pour Excel a également une fonction puissante qui s'appelle NOMBRE D'EXTRAITS, avec cette fonction, vous pouvez extraire rapidement uniquement les nombres des chaînes de texte d'origine.

Kutools pour Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours

Après l'installation de Kutools pour Excel, procédez comme suit:

1. Cliquez sur une cellule à côté de votre chaîne de texte où vous placerez le résultat, voir capture d'écran:

doc extrait les numéros seulement 5

2. Puis clique Kutools > Fonctions Kutools > Texte > NOMBRE D'EXTRAITS, voir capture d'écran:

doc extrait les numéros seulement 6

3. Dans le Arguments de fonction boîte de dialogue, sélectionnez une cellule dont vous souhaitez extraire les nombres du SMS zone de texte, puis entrez véritable or faux into the N zone de texte, voir capture d'écran:

doc extrait les numéros seulement 7

Notes: l'argument N est un élément facultatif, si vous entrez véritable, il renverra les nombres sous forme numérique, si vous entrez faux, il renverra les nombres au format texte, la valeur par défaut est false, vous pouvez donc le laisser vide.

4. Et puis cliquez OK, les nombres ont été extraits de la cellule sélectionnée, puis faites glisser la poignée de remplissage vers les cellules auxquelles vous souhaitez appliquer cette fonction, vous obtiendrez le résultat suivant:

doc extrait les numéros seulement 8

Cliquez pour télécharger et essayer gratuitement Kutools for Excel Now!


Méthode 4: Extraire le nombre décimal uniquement de la chaîne de texte avec formule

Si les chaînes de texte qui incluent des nombres décimaux dans votre feuille de calcul, comment pouvez-vous extraire uniquement les nombres décimaux des chaînes de texte?

La formule ci-dessous peut vous aider à extraire les nombres décimaux des chaînes de texte rapidement et facilement.

Entrez cette formule :=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")))), Puis remplissez la poignée jusqu'aux cellules que vous souhaitez contenir cette formule, tous les nombres décimaux ont été extraits des chaînes de texte, voir capture d'écran:

doc extrait les numéros seulement 13


Extraire le numéro uniquement des chaînes avec Kutools for Excel

Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!

Articles relatifs:


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
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.
    Daniyal · 3 months ago
    how to get a extract extact numbers from text

  • To post as a guest, your comment is unpublished.
    Adsi · 5 months ago
    Hi, I'm looking to list all numbers in a string before a certain character. The string has letters and numbers, so I want to remove the letters and only list the numbers that appear before the second minus sign "-". 
    Example:
    MUMUT-S941-22460991e002

    I want that to isolate the numbers 941 in the above example.
  • To post as a guest, your comment is unpublished.
    SunnySchindler · 6 months ago
    How to use Index instead of Indirect to extract number from string. Indirect is volatile and Index can replace it as per http://www.excelhero.com/blog/2011/03/the-imposing-index.html. Thanks Ahead!
  • To post as a guest, your comment is unpublished.
    Ivy Lee · 1 years ago
    Hello! I found the following formula to extract only the numbers from text strings in Excel very useful

    SUMPRODUCT(MID(0&A5, LARGE(INDEX(ISNUMBER(--MID(A5, ROW(INDIRECT("1:"&LEN(A5))), 1)) * ROW(INDIRECT("1:"&LEN(A5))), 0), ROW(INDIRECT("1:"&LEN(A5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A5)))/10)

    Could you explain more on this formula as it seems quite complicated? Many thanks.
  • To post as a guest, your comment is unpublished.
    Dinesh · 1 years ago
    Reading ID: 3151346 BeatO User ID: 239930 Name: Mahesh Phone: 9823010759 Email: City: nashik State: Maharashtra Reading: 55 Meal Time: Random Meal Type: RANDOM Reading Time: 2020-03-01 00:15:57 View user readings in Portal



    I want to extract numeric no. post Reading text
  • To post as a guest, your comment is unpublished.
    Sapphire · 1 years ago
    Hello, how can i extract the cheque no.only from the below text

    OUTWARD CLEARING CLRG CHQ DEPOSIT CHQ. NO: 000123~700320456 ABCD ARAB PLAZ BRANC Value DATE, 01/02/2020 - S79519642

    i tried below formula and its working but i need to change the 1:57 by calculating how many character before the first digit plus how many digits i want to extract.
    =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+5 im using this formula to know how many characters before the first number..

    =TEXTJOIN("",TRUE,IFERROR(MID(A3,ROW(INDIRECT("1:57")),1)+0,""))
    • To post as a guest, your comment is unpublished.
      Swadkar2 · 1 years ago
      =Mid(A2,find("CHQ. NO", A2)+9,6)
  • To post as a guest, your comment is unpublished.
    david.bidzhoian@gmail.com · 2 years ago
    Hi. I wonder to know is it possible to extract from the next string '102-105+106-10605-10605 -10631-10632-10633-10634-10635+107' all values of only three digits that have sign '-' (i.e. minus) before them (or plus - doesn't matter). Then extract extract all 5-digits values with the same rule? If this is possible to do I will save many many hours of manual work that I can devote to lot's of uselful tasks.

    P.S.
    I know that regular expressions are able to do that, BUT... I hate their syntax, so I'm searching any other method to complete this task.

    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    alamarridi@gmail.com · 2 years ago
    Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40

    Ali Baba Dark Chocolate 25g box 12 pcs
    Ali Baba Dark Chocolate 20g*24 box
    Cadbury 5 Star White Chocolate 15g
    Kinder 2 White Chocolate 25g*24
    ALpella Biscuits W/Marshmallow300g
    Alpella Chocolate 40g
    • To post as a guest, your comment is unpublished.
      patrick_mclachlan_br1234 · 1 years ago
      did anyone answer or did you figure this out? this is exactly my problem right - even down to the letter 'g'. the problem with the formula I am using now is that it returns the first number, if there are more than one, from the string and not the number I am wishing to return.
  • To post as a guest, your comment is unpublished.
    Satyaa1 · 2 years ago
    Hi, is it possible to extract the numbers before "g", for example : 25,20,15,25,300,40


    Ali Baba Dark Chocolate 25g box 12 pcs
    Ali Baba Dark Chocolate 20g*24 box
    Cadbury 5 Star White Chocolate 15g
    Kinder 2 White Chocolate 25g*24
    ALpella Biscuits W/Marshmallow300g
    Alpella Chocolate 40g
    • To post as a guest, your comment is unpublished.
      Shashikumar H · 1 years ago
      can any one answer this question, how to extract the no's
  • To post as a guest, your comment is unpublished.
    Veras · 2 years ago
    Tkssssss you saved my day :) God bless you
  • To post as a guest, your comment is unpublished.
    Alex · 2 years 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 years 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.
      skyyang · 2 years ago
      Hi, Aaron,
      For solving your problem, may be the below article can help you, please visit it:

      https://www.extendoffice.com/documents/excel/4563-excel-extract-last-number-from-string.html
  • To post as a guest, your comment is unpublished.
    Satya · 2 years 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 · 2 years 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 · 2 years ago
    This isn't working for decimals, it's removing decimal point.
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Shahid,
      To extract the decimal numbers from text strings, the below article may help you, please try.
      https://www.extendoffice.com/documents/excel/3271-excel-extract-decimal-number-from-string.html
  • To post as a guest, your comment is unpublished.
    josia · 3 years 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 · 2 years 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 · 3 years 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.
    Farhad72 · 3 years 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 · 3 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 · 3 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 · 4 years ago
    Thank you. Its great.
  • To post as a guest, your comment is unpublished.
    Matt · 4 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.
      415vac@gmail.com · 4 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.
    acimmino · 4 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 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 · 4 years ago
      Try the formula

      =MID(F14,FIND("$",F14),100)+0
  • To post as a guest, your comment is unpublished.
    Samoil · 5 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 · 4 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 · 4 years ago
      hi man can you explain how the formula works?
  • To post as a guest, your comment is unpublished.
    adrie farndell · 5 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 · 4 years ago
      =+MID(C14,5,FIND("X",C14)-1-LEN("coil"))
    • To post as a guest, your comment is unpublished.
      Giri · 4 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 · 6 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 · 6 years ago
    thanks, really helpful
  • To post as a guest, your comment is unpublished.
    Deepak · 6 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 · 6 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 · 6 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 · 6 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 · 6 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 · 5 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 · 6 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 · 7 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 · 7 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 · 7 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 · 7 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 · 7 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 · 7 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 · 7 years ago
    Who can explain this formula to me .