+ Reply to Thread
Results 1 to 3 of 3

Single cell formula to extract whole numbers?

  1. #1
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    225

    Single cell formula to extract whole numbers?

    Hello to the great Excel Forum!

    I have column in wich are data or no data( isblank(true))

    "The quick brown fox jumps over the lazy dog." =no numbers then no data, show "0" null.

    "The quick 1234brown fox jumps over the lazy dog." =1234

    "The quick 1234brown fox jumps4321 over the lazy dog." =1234+4321=5555

    "The quick 1234brown fox jumps over 1 the lazy dog." =1234+1=1235

    "The quick 1.234brown fox jumps over the lazy dog." =1234 <<dot EU thousand separator

    "The quick 1.234brown fox jumps over 1.000 the lazy dog." =1234+1000=2234
    etc.

    So, there always will be space" " before or after numbers. There might be dot"." inside number or not.
    There always will be let's say "The" at the beginning of cell text data.

    This formula works only for first numbers, though it's search for "otp:" inside cell:
    Please Login or Register  to view this content.
    Thanks in advance, God Bless!
    Last edited by B.W.B.; 04-03-2019 at 10:10 AM.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Single cell formula to extract whole numbers?

    Hi B.W.B,
    Here is a modified formula originally written by Bosco
    Enter formula in cell C245 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v B C
    244
    245 The quick 1234brown fox jumps over the lazy dog. 1234
    246 The quick 1234brown fox jumps4321 over the lazy dog. 5555
    247 The quick 1234brown fox jumps over 1 the lazy dog. 1235
    248 The quick 1.234brown fox jumps over the lazy dog. 1234
    249 The quick 1.234brown fox jumps over 1.000 the lazy dog. 2234
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    11-26-2014
    Location
    Bosnia&Herzegovina
    MS-Off Ver
    Office2013
    Posts
    225

    Re: Single cell formula to extract whole numbers?

    Quote Originally Posted by AlKey View Post
    Hi B.W.B,
    Here is a modified formula originally written by Bosco
    Enter formula in cell C245 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v B C
    244
    245 The quick 1234brown fox jumps over the lazy dog. 1234
    246 The quick 1234brown fox jumps4321 over the lazy dog. 5555
    247 The quick 1234brown fox jumps over 1 the lazy dog. 1235
    248 The quick 1.234brown fox jumps over the lazy dog. 1234
    249 The quick 1.234brown fox jumps over 1.000 the lazy dog. 2234
    Thanks Al, thanks Bosco, but...

    text Al's & Bosco I need:
    OTP: 31.003L, OSTATAK U S-6! 31009 0
    Nije mjer.temp na cisterni! 0 0
    Gorivo iz Kopra za BP Cazin 6.619L ostatak kupcima. 6619 6619
    Gorivo iz Ploča za bp Bugojno 0 0
    OTP: 31.007L, bp Jajce 4.013L,ostatak 25.504L kupcima 60524 29517


    What is: ROW($1:$100) i have couple of thousands rows!
    What is: "[<]\0"

    IF there is "otp:" then search for "," and after comma use Al-Bosco formula.
    IF there is R-6 or S-12 or whatever with minus then amend that number.

    It's just, i need whole number separated by space" ", it can be like: fox123 or 123fox or 123 fox, and "." dot also, if it's sepp with dot i need that numbr also.
    Last edited by B.W.B.; 04-03-2019 at 10:06 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract Numbers from a Single Cell
    By elaph in forum Excel General
    Replies: 2
    Last Post: 06-19-2017, 08:35 AM
  2. [SOLVED] Formula to extract numbers from cell?
    By ExcelBG in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2017, 09:08 AM
  3. How do I extract numbers from a single cell and multiply them together?
    By dsannito in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-21-2015, 03:14 PM
  4. Is there a formula that will extract the numbers to the left of the cell
    By benji1973 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-04-2015, 10:11 AM
  5. Extract numbers from two cell range into a single cell.
    By Len Farneth in forum Excel General
    Replies: 5
    Last Post: 01-24-2015, 08:53 PM
  6. [SOLVED] Extract single cell data based on a single cell entry
    By farmerdoode in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-05-2013, 11:11 PM
  7. [SOLVED] Formula to Extract Numbers from Text Cell
    By MHamid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 04:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1