Convert variable #'s in a gen. format to a # that can be used
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.
Thanks,
Steve
Gives me a
"Glenn" wrote:
The "beginning dashes" were intentional, they force Excel to evaluate the result
of the text manipulation (trim, right, substitute) as a number. What do you get
with them in the formula?
Steve wrote:
Mee too.
I don't know how they got their days, but you're right, I should go with
their ending #'s.
This formula only worked if I removed the begining dashes after the = .
=TRIM(RIGHT etc., but it did work that way.
The number came up, but by dividing the other number into it, it came up as
#value!
The # I'm using isnumber TRUE
The formula cell isnnumber FALSE
Even formatting that cell as a number from general, it's still FALSE
"Glenn" wrote:
OK, now I'm confused. There are 90 days between 10/31/2009 and 1/29/2010, but
the answer you want is 73? Let's go back to grabbing the last number in the cell:
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A1),":",REPT(" ",10)),10))
That should account for any extra spaces, anywhere within the text or after the
number at the end.
Steve wrote:
Whoa !! This was sooo weird, but I think I solved the mystery thanks to you
guiding me with your questions, though I still need the formula adjusted.
Both your MidTrimFind and the MidFind worked when I copied the data. And by
pasting that into the original cell, it worked. But both were identical, or
so I thought.
They were in different fonts, so when I put the original below what I typed
and then made them both in the same font, I discovered that the original had
extra spaces around the dashes, and before the number.
See below:
Analysis Period: 10/31/2009 to 01/29/2010 - Processing Days:73 typed
Analysis Period: 10/31/2009 to 01/29/2010 - Processing Days: 73
original
Anyway, if you could adjust your formula to account for these extra spaces,
I would be very grateful.
And again, Thanks for all your patience. It is much appreciated.
Steve
"Glenn" wrote:
Are you copying the text from your worksheet to the post, or re-typing it?
Try this:
=MID(TRIM(A3),FIND(" to ",TRIM(A3))+4,10)-
MID(TRIM(A3),FIND(" to ",TRIM(A3))-10,10)
Glenn wrote:
When I copy your text directly from this post to cell A3 in a blank
worksheet and then copy my formula to any other cell, I get the number
result 6. Can you reproduce that result?
Steve wrote:
This in the cell
Analysis Period: 01/23/2010 to 01/29/2010 - Processing Days: 6
the subtraction would work, as that's really all I need, the days, but
this
=MID(A3,FIND(" to ",A3)+4,10)-MID(A3,FIND(" to ",A3)-10,10)
still produced a #value!
Also =isnumber(e19) produced a TRUE
Thanks for your patience.
.
.
.
|