Convert variable #'s in a gen. format to a # that can be used
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.
.
|