+ Reply to Thread
Results 1 to 7 of 7

Sum & Separate numbers in text

  1. #1
    Registered User
    Join Date
    06-22-2010
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question Sum & Separate numbers in text

    Formats didn't hold below so I attached a spread sheet to make the columns and rows clearer.Below is a sample of what I have mixed over 45 cells. For all cells in column A that are "FIC" I need the dollars (that are mixed with text in column C) totaled (Column B would be blank). For all cells in column A that are "Outside". I need Column C to just be the dollars (no text). If Column A is "GSU" then I need the number following "Total Value" in column C.
    A B C
    1 FIC IV1234 MV: $26,453.42***Blah Blah text (IRA)
    2 FIC NV1234 MV: $1,307.76***Blah Blah text (IRA)
    3 Outside 12345 Market Value: $251,655.62***Blah Blah text
    4 GSU 6Z1234 $35.29***MONEY $42,676.95***(PNRA) Total Value: $42,712.24

    How can I get totaled and formated something like this:
    A B C
    5) FIC $27,761.19
    6) Outside 4393829 $251,655.62
    7) GSU 6ZE155196 $42,712.24

    Any suggestions?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Rahul Nagar's Avatar
    Join Date
    09-18-2008
    Location
    nasik
    MS-Off Ver
    Excel 2003
    Posts
    465

    Re: Sum & Seperate numbers in text

    Hi

    You can use find and replace option.
    find IV and replace with blank
    find NV and replace with blank

    I think this would be help you.

    Regard
    Rahul
    __________________
    Regards
    Rahul Nagar
    Founder of www.myshortcutkeys.com.


    If you get the answer of your questions then please click EDIT in your original post then click on GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
    Also
    If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum & Seperate numbers in text

    if its always in the same format with the amouny after : $
    see attached
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Valued Forum Contributor Rahul Nagar's Avatar
    Join Date
    09-18-2008
    Location
    nasik
    MS-Off Ver
    Excel 2003
    Posts
    465

    Re: Sum & Seperate numbers in text

    this is the best one

  5. #5
    Registered User
    Join Date
    06-22-2010
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Sum & Seperate numbers in text

    Wow that is terrific! Can you help me understand your formula so I can recreate it on the actual spread sheet?
    =MID(SUBSTITUTE(D11,"*",REPT(" ",10)),FIND(":",SUBSTITUTE(D11,"*",REPT(" ",10)))+3,15)+0

    I get the MID function, but not the substitute and repeat functions. I currently get an "#Value!" error.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Sum & Separate numbers in text

    SUBSTITUTE(D11,"*",REPT(" ",10))
    replaces * with space repeated 10 times so it becomes
    Please Login or Register  to view this content.
    and in the second example
    Please Login or Register  to view this content.

    that gives room for the mid function to extract 15 characters after : $
    Last edited by martindwilson; 06-22-2010 at 11:12 AM.

  7. #7
    Registered User
    Join Date
    06-22-2010
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Sum & Separate numbers in text

    Terrific Thank You

+ 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