Excel formula help

J

johhny

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.
 
G

Guest

Hi

Try something like this:

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

Andy.
 
A

anilsolipuram

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))
 
C

CLR

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
 
J

johhny

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
 
C

CLR

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
Click to expand...
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
 
Top