Results 1 to 7 of 7

Extract Numbers and Decimals from Text

Threaded View

PY_ Extract Numbers and Decimals... 11-12-2010, 03:31 PM
martindwilson Re: Extract Numbers and... 11-12-2010, 03:43 PM
Ron Coderre Re: Extract Numbers and... 11-12-2010, 03:59 PM
PY_ Re: Extract Numbers and... 11-12-2010, 04:09 PM
Ron Coderre Re: Extract Numbers and... 11-12-2010, 04:10 PM
squiggler47 Re: Extract Numbers and... 11-12-2010, 04:00 PM
PY_ Re: Extract Numbers and... 11-12-2010, 04:03 PM
  1. #1
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Extract Numbers and Decimals from Text

    I have a handfull of different text layouts that i need to extract the numbers along with their decimals from. The text layouts change so using a generic code to just extract the numbers+decimals seems the way to go.

    I found a code here that will amost work:
    http://www.mrexcel.com/forum/showthread.php?t=362184

    Using this Formula and CTRL+Shift to apply it:
    =SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

    Using the above, the following gets converted like this:
    6.141 ft by 40.552 ft - Results in 614140552

    The problem is, i want to be able to seperate out the numbers by a space and i do not want to lose the decimal point. Any idea how i can do that?


    Change This:
    6.141 ft by 40.552 ft
    To This:
    6.141 40.552

    Change This:
    4.188 ft by 25 ft at 13.54 ft
    To This:
    4.188 25 13.54


    I can take the result and split them out as needed into different cells after that.
    Last edited by PY_; 11-12-2010 at 04:10 PM.

Thread Information

Users Browsing this Thread

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

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