Excel formula help

  • Thread starter Thread starter johhny
  • Start date Start date
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.
 
Hi

Try something like this:

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

Andy.
 
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))
 
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
 
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
 
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
 
Hello!

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

email: (e-mail address removed)


Regards
Johhny
 
Back
Top