+ Reply to Thread
Results 1 to 11 of 11

Extract Numbers from within Inconsistent Text

  1. #1
    Registered User
    Join Date
    06-13-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Extract Numbers from within Inconsistent Text

    I am trying to figure out a formula that would give me columns of "Drive C Space", "Drive C Used", "Drive D Space", "Drive D Used", "Drive Y Space", "Drive Y Used", etc. from inconsisitent data like this (these are all in one column):

    Drive Letter: - C: / Drive Size: - 60 / Free Space: - 46Drive Letter: - D: / Drive Size: - 76 / Free Space: - 70
    Drive Letter: - C: / Drive Size: - 60 / Free Space: - 45Drive Letter: - D: / Drive Size: - 76 / Free Space: - 70
    Drive Letter: - A: / Drive Size: - 0 / Free Space: - 0Drive Letter: - C: / Drive Size: - 24 / Free Space: - 12Drive Letter: - D: / Drive Size: - 9 / Free Space: - 9Drive Letter: - X: / Drive Size: - 4 / Free Space: - 0Drive Letter: - Y: / Drive Size: - 0 / Free Space: - 0
    Drive Letter: - C: / Drive Size: - 60 / Free Space: - 46Drive Letter: - D: / Drive Size: - 76 / Free Space: - 70
    Drive Letter: - C: / Drive Size: - 60 / Free Space: - 43Drive Letter: - D: / Drive Size: - 76 / Free Space: - 69
    Drive Letter: - C: / Drive Size: - 60 / Free Space: - 46Drive Letter: - D: / Drive Size: - 76 / Free Space: - 70
    Drive Letter: - A: / Drive Size: - 0 / Free Space: - 0Drive Letter: - C: / Drive Size: - 24 / Free Space: - 11Drive Letter: - D: / Drive Size: - 9 / Free Space: - 8Drive Letter: - L: / Drive Size: - 100 / Free Space: - 82Drive Letter: - P: / Drive Size: - 100 / Free Space: - 71Drive Letter: - X: / Drive Size: - 4 / Free Space: - 0Drive Letter: - Y: / Drive Size: - 0 / Free Space: - 0
    Drive Letter: - C: / Drive Size: - 59 / Free Space: - 34Drive Letter: - D: / Drive Size: - 350 / Free Space: - 309Drive Letter: - E: / Drive Size: - 558 / Free Space: - 481Drive Letter: - O: / Drive Size: - 0 / Free Space: - 0Drive Letter: - V: / Drive Size: - 1000 / Free Space: - 739Drive Letter: - W: / Drive Size: - 1600 / Free Space: - 158Drive Letter: - Y: / Drive Size: - 0 / Free Space: - 0

    Any ideas would be greatly appreciated.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Extract Numbers from within Inconsistent Text

    Hi Sebringm,

    welcome to the forum.
    Please upload a sample excel workbook so that data layout becomes clear.... thx


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    06-13-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Extract Numbers from within Inconsistent Text

    Sorry, here is a sample. As you can see column A has extensive Drive detail.
    Attached Files Attached Files

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Extract Numbers from within Inconsistent Text

    try something like this if applicable to you. addjust and experiment on the numbers and text search.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Extract Numbers from within Inconsistent Text

    This beastly thing works (for sample data anyways)
    In B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag across and down

    Working on a better one, but taking a while , so figured I'd offer a working solution anyways ..
    See Attachment

    Hope this helps
    Attached Files Attached Files
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract Numbers from within Inconsistent Text

    I'd be tempted to go this way.

    For 2007 and above, you could substitute $B2 in the formula in C2, with the formula in B2,
    and if you are particularly masochistic, you could leave out A2:A4 and put the values directly into the B2 formula.

    This should work for different string lengths, i.e. more drive groups, just drag C2 across and down to suit.

    [EDIT]
    Note that the Drive letters are not always consistant within the same column.
    Attached Files Attached Files
    Last edited by Marcol; 02-26-2013 at 08:24 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Registered User
    Join Date
    06-13-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Extract Numbers from within Inconsistent Text

    Dredwolf, that is AMAZING!!! Works great!! It will take me a while to decifer how it is working, but thank you SO much!

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract Numbers from within Inconsistent Text

    Here's a simpler way to keep the columns to specific drive letters.

    Add columns to suit the Drive Letter you need.

    This could be even simpler if you want to eliminate the letter columns.

    See sheet 3
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-13-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Extract Numbers from within Inconsistent Text

    Marcol, that is a great way too! Thank you.

    Thank you to all for your assistance!

  10. #10
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Extract Numbers from within Inconsistent Text

    You are welcome
    And Thank You for the STAR tap, always appreciated!

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Extract Numbers from within Inconsistent Text

    Quote Originally Posted by Marcol View Post
    ....
    This could be even simpler if you want to eliminate the letter columns.
    Just to tidy up this thread, see the attached
    Add columns to suit the Drive Letter you need.
    Attached Files Attached Files

+ Reply to Thread

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