Finding the value of a cell
Posted by Brian on February 12, 2002 8:21 AM
I am trying to loop through a row of cells to add up totals. In some cells, it may look like this
OPEN(8.5)
The number in the parinthasis is what I am looking for. I learned yesterday about using the left function. Is there a function that will allow me to look at the middle of a cell or variable?
Thanks, you all are a huge help!
Brian
Posted by Tom Urtis on February 12, 2002 8:29 AM
If your values are all in that same syntax of OPEN(#.#), then this would extract that:
=MID(A1,6,3)
If the values change in amounts and number of digits -- example OPEN(652.31), or if there is ever a space between OPEN and the parantheses, then there is a more complicated formula. So first, does this first one do what you want, or are there variations in the entire string from which you are trying to extract the parentesized figures?
Tom Urtis
Posted by Tom Urtis on February 12, 2002 8:33 AM
By the way...
This formula
=MID(A1,6,3)+0
would treat the extracted value as a number instead of as a text return, to be on the safe side in case you'll be using it for future calculations.
T.U.
Posted by Brian on February 12, 2002 8:34 AM
The possible values would be a single digit, double digit, or a combination of both with a decimal of 1 or 2 places. This is a time sheet.
OPEN(8)
OPEN(8.25)
OPEN(10)
OPEN(8.5)
OPEN(10.25)
OPEN(10.5)
I guess that would be all the combinations that it could be (thought the numbers could be different).
Posted by Tom Urtis on February 12, 2002 9:35 AM
Try this formula, it should accommodate the differences in your values in between parentheses.
=IF(ISNUMBER(SEARCH("(",A2)),RIGHT(SUBSTITUTE(A2,")",""),(LEN(A2)-1)-SEARCH("(",A2)),A2)+0
HTH
Tom Urtis
Posted by Mark W. on February 12, 2002 10:33 AM
Or... =MID(A2,FIND("(",A2)+1,LEN(A2)-6)+0 [nt]
Posted by Aladin Akyurek on February 12, 2002 10:55 AM
Is OPEN the only text bit or is there also a CLOSED or more like this?
======== The possible values would be a single digit, double digit, or a combination of both with a decimal of 1 or 2 places. This is a time sheet.
Posted by Tom Urtis on February 12, 2002 11:31 AM
The only thing is...
If his syntax changes (such as OPEN (#[#].[##]) with a space between OPEN and the numbers) then the formula I suggested would accommodate that. I'm not sure yours would Mark, unless I'm mistaken (not unheard of with me and formulas).
Thanks Mark.
Tom Urtis
Posted by Mark W. on February 12, 2002 12:12 PM
I didn't consider that possiblity from the problem statement, but...
...in that case I'd use...
=MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)+0
...and let number coercion extract the number! If his syntax changes (such as OPEN (#[#].[##]) with a space between OPEN and the numbers) then the formula I suggested would accommodate that. I'm not sure yours would Mark, unless I'm mistaken (not unheard of with me and formulas). Thanks Mark.
Posted by Aladin Akyurek on February 12, 2002 1:45 PM
OK, A different Approach
If OPEN(a-number-w/o-decimals) is the generic bit of interest, use:
=SUBSTITUTE(A1,"OPEN","")*-1
If there is also the opposite of OPEN, say, CLOSED having the same stucture, use:
=SUBSTITUTE(SUBSTITUTE(A1,"OPEN",""),"CLOSED","")*-1
Caveat. The above trick will work only if what is in between parens is a positive number.
Aladin
========= Is OPEN the only text bit or is there also a CLOSED or more like this? ======== : The possible values would be a single digit, double digit, or a combination of both with a decimal of 1 or 2 places. This is a time sheet. : OPEN(8)
Posted by Mark W. on February 12, 2002 1:57 PM
Too COOL!! But, why not =-SUBSTITUTE(A1,"OPEN","") [nt]
=SUBSTITUTE(A1,"OPEN","")*-1 If there is also the opposite of OPEN, say, CLOSED having the same stucture, use: =SUBSTITUTE(SUBSTITUTE(A1,"OPEN",""),"CLOSED","")*-1 Caveat. The above trick will work only if what is in between parens is a positive number. Aladin ========= : Is OPEN the only text bit or is there also a CLOSED or more like this? : ========
Posted by Aladin Akyurek on February 12, 2002 2:06 PM
Re: Too COOL!! But, why not =-SUBSTITUTE(A1,"OPEN","") [nt]
Thanks. Why not indeed? Esthetics maybe... & also I thought a pos number was/is expected after stripping off OPEN...
========== : If OPEN(a-number-w/o-decimals) is the generic bit of interest, use
