+ Reply to Thread
Results 1 to 11 of 11

Extract Numbers in Mixed Cell (not string)

  1. #1
    Registered User
    Join Date
    07-09-2015
    Location
    Virginia, US
    MS-Off Ver
    14.5.2 2011
    Posts
    4

    Question Extract Numbers in Mixed Cell (not string)

    Hello,

    The data spreadsheet I am working with is really messed up and there are SEVERAL entries in one cell with mixed text, numbers, and spaces. In just ONE cell there is:

    Example 1
    2006: Agri-Appraisal Co (200.00), Chamblin, Jack (200.00), Crocker, Robert (200.00), Jones, Jr., Donald (1,000.00)

    Example 2
    (1998) George Genseal $200; Mary J. Stambaugh $250; Al E. Stock $250 (1998) Non-Itemized $3,349

    I want to extract the dollar amounts. In example 1, I want the numbers in parenthesis. In example 2, i want the numbers with the dollar signs. I am most comfortable with equations but I am open to other options.

    Thank you in advance

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Numbers in Mixed Cell (not string)

    Edited: Formula needed fine tuning. I forgot to put an error check in.

    This seems to take care of the amounts in parentheses. Without a larger sampling it is difficult to tell. Assuming the string is in cell A1 array-enter this formula in B1 and fill across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    I'm still working on the other pattern. It's a tough one.
    Last edited by FlameRetired; 07-09-2015 at 10:27 PM.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Numbers in Mixed Cell (not string)

    This is the other one. Again, it works with the string supplied. I don't know how it will behave with the rest of your data.

    Same drill. Array-enter in cell B2 and fill across until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    These can probably be streamlined. I don't know, but I was challenged just to come up with a solution.

    Results are formatted currency.

    File is attached.

    Edit: One of the steps in this formula includes appending a ";" to a zero. If any other dollar amounts are like this one (space following, not ";") then this formula is going to fail.
    Last edited by FlameRetired; 07-09-2015 at 11:11 PM.

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

    Re: Extract Numbers in Mixed Cell (not string)

    Enter this regular formula in B1 and pull it across and then down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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

  5. #5
    Registered User
    Join Date
    07-09-2015
    Location
    Virginia, US
    MS-Off Ver
    14.5.2 2011
    Posts
    4

    Re: Extract Numbers in Mixed Cell (not string)

    Thank you so much to FlameRetired and AlKey. I appreciate your help.

    However, I cannot get any of the formulas to work. For example, for FlameRetired's response to example 1, instead of extracting all the numbers in the parentheses, it only pulls out the first, or 200.

    I experienced similar results with the other formulas. Do you guys have any idea what I could be doing wrong?

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

    Re: Extract Numbers in Mixed Cell (not string)

    Please see attached file with formulas.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-09-2015
    Location
    Virginia, US
    MS-Off Ver
    14.5.2 2011
    Posts
    4

    Thumbs up Re: Extract Numbers in Mixed Cell (not string)

    Thank you for the reply AlKey.

    I see now what I was doing wrong. Yet, the formula is still not working perfectly probably because my data is so bad. I seem to be having problems with the number I want to extract has digits after zero, example, .57. Yet, when I change the number to a whole number, it still does not read it. Maybe that is not the problem. Attached is the full dataset I am trying to extract. I appreciate any suggestions.


    AlKeyFromMagic789.xlsx

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Numbers in Mixed Cell (not string)

    Quote Originally Posted by magic789 View Post
    Thank you so much to FlameRetired and AlKey. I appreciate your help.

    However, I cannot get any of the formulas to work. For example, for FlameRetired's response to example 1, instead of extracting all the numbers in the parentheses, it only pulls out the first, or 200.

    I experienced similar results with the other formulas. Do you guys have any idea what I could be doing wrong?
    In the file attached to post #3 you'll notice starting in cell B1 in the formula bar that all the formulas have {} curly braces around them. Excel does this for you when you array-enter the formula not just with Enter but (in Edit mode) by simultaneously pressing and holding Ctrl + Shift keys down while hitting enter. Once done in cell B1 grab the fill handle and drag across as many columns as you need till you get blanks.

    You do not type the {} in yourself. If you try it will not work.

    What you describe sounds like the first cell was not array-entered.

    Let me know if it still isn't working for you.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Numbers in Mixed Cell (not string)

    I made a copy of the sheet in AlKeyFromMagic789 (Sheet1 (2)). I've highlighted 3 rows where I applied my formulas. Two for "(" and one for "$".

    They both work, but I had no idea the strings were so long. Array formulas are resource hungry and it shows in this upload.

    Personally now that I've seen this I wouldn't recommend using mine.

    The reworked file is attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-09-2015
    Location
    Virginia, US
    MS-Off Ver
    14.5.2 2011
    Posts
    4

    Talking Re: Extract Numbers in Mixed Cell (not string)

    WOW FlameRetired! Its working!!!!!!!!!

    This is wonderful. I am very grateful for both of your help.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract Numbers in Mixed Cell (not string)

    Glad to hear it.

+ 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 Mixed (Text + Numeric) Data
    By asimjavedcma in forum Excel General
    Replies: 1
    Last Post: 01-27-2015, 05:37 AM
  2. Extract numbers from a string
    By mkhan123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-03-2011, 03:22 PM
  3. Extract numbers from string
    By gavster in forum Excel General
    Replies: 2
    Last Post: 11-18-2010, 12:29 PM
  4. Replies: 2
    Last Post: 06-18-2010, 05:10 PM
  5. Replies: 2
    Last Post: 08-23-2005, 03:49 AM

Tags for this Thread

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