+ Reply to Thread
Results 1 to 9 of 9

Excel formula help

  1. #1
    Registered User
    Join Date
    07-03-2005
    Posts
    4

    Excel formula help

    Hello!

    Can somone help me with this formula

    2004:21-0-1-1-11 in this cell i wan't to get out "21"

    2005:2-0-0-0-1 in this cell i wan't to get out "2"

    My result from the formula is "-21" and ":2-" is there a formula that i can use on both and get the result "21" and "2"?

    I want to add the together at the end and get the result "23" but with : and - i only get error.

    The next problems is that i have copied some formulas from spreadsheet 1 to spreadsheet 2 these formulas are still reffering to spreadsheet 1. is there an easy way to fix that the formuals copied to spreadsheet 2 does reffer to the copied spreadsheet.?


    Regards
    Johhny
    Last edited by johhny; 07-03-2005 at 02:22 PM.

  2. #2
    Forum Contributor
    Join Date
    11-09-2004
    Posts
    451
    if say a1 has 2004:21-0-1-1-11
    the formulae to retrieve 21 is

    =MID(a1,FIND(":",a1,1)+1,FIND("-",a1,FIND(":",a1,1))-(FIND(":",a1,1)+1))

  3. #3
    Guest

    Re: Excel formula help

    Hi

    Try something like this:

    =VALUE(MID(D11,FIND(":",D11)+1,FIND("-",D11)-1-FIND(":",D11)))
    where your data is in D11

    Andy.


    "johhny" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello!
    >
    > Can somone help me with this formula
    >
    > 2004:21-0-1-1-11 in this cell i wan't to get out "21"
    >
    > 2005:2-0-0-0-1 in this cell i wan't to get out "2"
    >
    > My result from the formula is "-21" and ":2-" is there a formula that i
    > can use on both and get the result "21" and "2"?
    >
    > I want to add the together at the end and get the result "23" but with
    > : and - i only get error.
    >
    >
    > --
    > johhny
    > ------------------------------------------------------------------------
    > johhny's Profile:
    > http://www.excelforum.com/member.php...o&userid=24877
    > View this thread: http://www.excelforum.com/showthread...hreadid=384182
    >




  4. #4
    CLR
    Guest

    Re: Excel formula help

    If you have LOTS of these in a column, then you can do Data > TextToColumns
    and separate your strings into separate columns, then easily pick out the
    values you wish to sum.............

    Vaya con Dios,
    Chuck, CABGx3


    "johhny" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello!
    >
    > Can somone help me with this formula
    >
    > 2004:21-0-1-1-11 in this cell i wan't to get out "21"
    >
    > 2005:2-0-0-0-1 in this cell i wan't to get out "2"
    >
    > My result from the formula is "-21" and ":2-" is there a formula that i
    > can use on both and get the result "21" and "2"?
    >
    > I want to add the together at the end and get the result "23" but with
    > : and - i only get error.
    >
    >
    > --
    > johhny
    > ------------------------------------------------------------------------
    > johhny's Profile:

    http://www.excelforum.com/member.php...o&userid=24877
    > View this thread: http://www.excelforum.com/showthread...hreadid=384182
    >




  5. #5
    Registered User
    Join Date
    07-03-2005
    Posts
    4

    not working

    Hello!

    Thanks for all the reply

    When i try to put some of these formulas in i only get that it is a error in the formula. I have specified the right CELL.

    The suggestion about Data--> it seperates the formula inside the CELL how can i get pass that?

    I have copied all my formulas from "work1" to "work2" how can i get these to reffer to "work2" all of copied formulas are still reffering to "work1".


    Regards

    Johhny

  6. #6
    CLR
    Guest

    Re: Excel formula help

    It's hard to tell exactly what's going on with your sheet Johhny since you
    have not given us any cell locations of your values and formulas. I tried
    both Andy's and Anilsolipuram's formulas and they both seemed to work fine
    to do the separation. Neither do the sum you're requesting at the end. It
    was assumed that you would take care of that part. On second read, the Data
    > TextToColumns method would not be practical unless you were dealing with

    two long columns of these type strings and needed to extract from them.

    If you're still having trouble, post back giving the cell locations of your
    strings and where you want the formulas and the sum result and someone will
    be glad to help.........

    Vaya con Dios,
    Chuck, CABGx3




    "johhny" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello!
    >
    > Thanks for all the reply
    >
    > When i try to put some of these formulas in i only get that it is a
    > error in the formula. I have specified the right CELL.
    >
    > The suggestion about Data--> it seperates the formula inside the CELL
    > how can i get pass that?
    >
    > I have copied all my formulas from "work1" to "work2" how can i get
    > these to reffer to "work2" all of copied formulas are still reffering
    > to "work1".
    >
    >
    > Regards
    >
    > Johhny
    >
    >
    > --
    > johhny
    > ------------------------------------------------------------------------
    > johhny's Profile:

    http://www.excelforum.com/member.php...o&userid=24877
    > View this thread: http://www.excelforum.com/showthread...hreadid=384182
    >




  7. #7
    Registered User
    Join Date
    07-03-2005
    Posts
    4

    email

    Hello!

    IS it possible that you can send me an e-mail with and attach the xls file with the formula typed in?

    email: [email protected]


    Regards
    Johhny

  8. #8
    CLR
    Guest

    Re: Excel formula help

    It's on it's way...........

    Vaya con Dios,
    Chuck, CABGx3


    "johhny" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello!
    >
    > IS it possible that you can send me an e-mail with and attach the xls
    > file with the formula typed in?
    >
    > email: [email protected]
    >
    >
    > Regards
    > Johhny
    >
    >
    > --
    > johhny
    > ------------------------------------------------------------------------
    > johhny's Profile:

    http://www.excelforum.com/member.php...o&userid=24877
    > View this thread: http://www.excelforum.com/showthread...hreadid=384182
    >




  9. #9
    Registered User
    Join Date
    07-03-2005
    Posts
    4

    thanks

    Thank you all for the help

+ 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