I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve




One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve

I'm getting a #value!
A1 is where my general format data is, correct ?

"Glenn" wrote:

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve




One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))
.

I do believe the "Days" will always be present, and your formula works to
produce either the single #, tens, or hundreds, but when I use that result as
a divisor, it still produces a #value!
E.g. Formula result is 6(in J3).... e19/j3 = #value!

"T. Valko" wrote:

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve




.

E.g. Formula result is 6(in J3).... e19/j3 = #value!

What's in E19?

When a formula like that resturns a #VALUE! error it usually means one (or
possibly both) cells referenced contain TEXT. Since we know that J3 contains
numeric 6 then the problem has to be with cell E19.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I do believe the "Days" will always be present, and your formula works to
produce either the single #, tens, or hundreds, but when I use that result
as
a divisor, it still produces a #value!
E.g. Formula result is 6(in J3).... e19/j3 = #value!

"T. Valko" wrote:

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last,
last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a
divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6
or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable
#.

Thanks,

Steve




.

Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You
probably have trailing spaces in your data, which could cause the #Value! error.
Try this instead:

B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3)

Steve wrote:
I'm getting a #value!
A1 is where my general format data is, correct ?

"Glenn" wrote:

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve



One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))
.

This produced :6
I changed the 3's to 2's and got the 6, but I don't think my fix will
account for 2 or 3 digit numbers, will it ? Same as other, taking a number in
cell E19, and dividing by the resulting 6, still produces a #value!


"Glenn" wrote:

Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You
probably have trailing spaces in your data, which could cause the #Value! error.
Try this instead:

B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3)

Steve wrote:
I'm getting a #value!
A1 is where my general format data is, correct ?

"Glenn" wrote:

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve



One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))
.

.

E19 has a number in it. Formatted as a number.
Also, in playing around, I may have changed the formatting of J3. It is
currently formatted as a number.

Thanks,

Steve

"T. Valko" wrote:

E.g. Formula result is 6(in J3).... e19/j3 = #value!

What's in E19?

When a formula like that resturns a #VALUE! error it usually means one (or
possibly both) cells referenced contain TEXT. Since we know that J3 contains
numeric 6 then the problem has to be with cell E19.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I do believe the "Days" will always be present, and your formula works to
produce either the single #, tens, or hundreds, but when I use that result
as
a divisor, it still produces a #value!
E.g. Formula result is 6(in J3).... e19/j3 = #value!

"T. Valko" wrote:

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last,
last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a
divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6
or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable
#.

Thanks,

Steve




.



.

What EXACTLY is in A1 (or whatever cell it is that you are looking at)?

Maybe try this, which assumes you are just subtracting the first date from the
second date to get "Days":

=MID(A1,FIND(" to ",A1)+4,11)-MID(A1,FIND(" to ",A1)-10,11)

As for your other problem, put this in a blank cell and tell us what the result is:

=ISNUMBER(E19)


Steve wrote:
This produced :6
I changed the 3's to 2's and got the 6, but I don't think my fix will
account for 2 or 3 digit numbers, will it ? Same as other, taking a number in
cell E19, and dividing by the resulting 6, still produces a #value!


"Glenn" wrote:

Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You
probably have trailing spaces in your data, which could cause the #Value! error.
Try this instead:

B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3)

Steve wrote:
I'm getting a #value!
A1 is where my general format data is, correct ?

"Glenn" wrote:

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve


One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))
.

.

Glenn wrote:

Maybe try this, which assumes you are just subtracting the first date
from the second date to get "Days":

=MID(A1,FIND(" to ",A1)+4,11)-MID(A1,FIND(" to ",A1)-10,11)


Actually, both 11's should be 10's.

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.



"Glenn" wrote:

What EXACTLY is in A1 (or whatever cell it is that you are looking at)?

Maybe try this, which assumes you are just subtracting the first date from the
second date to get "Days":

=MID(A1,FIND(" to ",A1)+4,11)-MID(A1,FIND(" to ",A1)-10,11)

As for your other problem, put this in a blank cell and tell us what the result is:

=ISNUMBER(E19)


Steve wrote:
This produced :6
I changed the 3's to 2's and got the 6, but I don't think my fix will
account for 2 or 3 digit numbers, will it ? Same as other, taking a number in
cell E19, and dividing by the resulting 6, still produces a #value!


"Glenn" wrote:

Yes, A1 should have "Period: 01/23/2010 to 01/29/2010 - Days: 6". You
probably have trailing spaces in your data, which could cause the #Value! error.
Try this instead:

B1 =--RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",3)),3)

Steve wrote:
I'm getting a #value!
A1 is where my general format data is, correct ?

"Glenn" wrote:

Steve wrote:
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last, last 2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want: E29/6 or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a usable/divisable #.

Thanks,

Steve


One way:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))
.

.

.

If this string is in cell A1:

Period: 01/23/2010 to 01/29/2010 - Days: 6

What result do you get from this formula:

=CODE(RIGHT(A1))

That formula should return 54. That's the character code value for the
number 6. If you get a result other than 54 then that means there is some
unseen whitespace character(s) at the end of the string in cell A1.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
E19 has a number in it. Formatted as a number.
Also, in playing around, I may have changed the formatting of J3. It is
currently formatted as a number.

Thanks,

Steve

"T. Valko" wrote:

E.g. Formula result is 6(in J3).... e19/j3 = #value!

What's in E19?

When a formula like that resturns a #VALUE! error it usually means one
(or
possibly both) cells referenced contain TEXT. Since we know that J3
contains
numeric 6 then the problem has to be with cell E19.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I do believe the "Days" will always be present, and your formula works
to
produce either the single #, tens, or hundreds, but when I use that
result
as
a divisor, it still produces a #value!
E.g. Formula result is 6(in J3).... e19/j3 = #value!

"T. Valko" wrote:

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last,
last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a
divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want:
E29/6
or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a
usable/divisable
#.

Thanks,

Steve




.



.

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.

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.

I am getting the 54.

Actually this is the actual wording in the cell. I inadvertently left out
the word Analyis originally.
Analysis Period: 01/23/2010 to 01/29/2010 - Processing Days: 6



"T. Valko" wrote:

If this string is in cell A1:

Period: 01/23/2010 to 01/29/2010 - Days: 6

What result do you get from this formula:

=CODE(RIGHT(A1))

That formula should return 54. That's the character code value for the
number 6. If you get a result other than 54 then that means there is some
unseen whitespace character(s) at the end of the string in cell A1.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
E19 has a number in it. Formatted as a number.
Also, in playing around, I may have changed the formatting of J3. It is
currently formatted as a number.

Thanks,

Steve

"T. Valko" wrote:

E.g. Formula result is 6(in J3).... e19/j3 = #value!

What's in E19?

When a formula like that resturns a #VALUE! error it usually means one
(or
possibly both) cells referenced contain TEXT. Since we know that J3
contains
numeric 6 then the problem has to be with cell E19.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I do believe the "Days" will always be present, and your formula works
to
produce either the single #, tens, or hundreds, but when I use that
result
as
a divisor, it still produces a #value!
E.g. Formula result is 6(in J3).... e19/j3 = #value!

"T. Valko" wrote:

One way...

Assuming the word "Days:" is *always* present.

=--MID(A1,SEARCH("Days:",A1)+5,5)

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have this data that is pasted to my worksheet:

Period: 01/23/2010 to 01/29/2010 - Days: 6
It is in general format. The day numbers will always be in the last,
last
2
, or last 3 positions.
Depending on the period, the days may be any # up to the 365.
I need to use that day #, be it 6, or 25, or 147, whatever, as a
divisor
of
another number. E.g. if 6, then 1234 / 6 = 206
if 25, then 5678 / 25 = 227, etc/
The variable #'s will be in the same cell, so I basically want:
E29/6
or
E29/25, or E29/147, etc.
Basically, those general formatted #'s converted to a
usable/divisable
#.

Thanks,

Steve




.



.



.

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.


.

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.


.

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.


.

.

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.


.

.

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.


.

.

.

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

Analysis Period: 01/23/2010 to 01/29/2010 - Processing Days: 6


"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

Can not reproduce the error with information you are providing. If you like,
email me a *SMALL* sample worksheet with the error and I'll take a look at it.
Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If you like,
email me a *SMALL* sample worksheet with the error and I'll take a look at it.
Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

The character after the colon is not a "normal" space. I copied the text from
A3 into A1 of a new worksheet, then entered the following two formulas:


B1 = MID($A$1,ROW(),1)
C1 = CODE(MID($A$1,ROW(),1))


I copied B1 and C1 down the columns until the entire string was evaluated. The
spaces between words ("Analysis" and "Period", "Processing" and "Days") is
CHAR(32), all other spaces are CHAR(160).

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A3,CHAR(160),REPT(" ",10)),10))


Steve wrote:
Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If you like,
email me a *SMALL* sample worksheet with the error and I'll take a look at it.
Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

Thank you so very, very much. I really appreciate all your patience.
I don't think I even want to begin to know what a non-normal space is, but
the final formula works great.

Thank you again,

Steve

"Glenn" wrote:

The character after the colon is not a "normal" space. I copied the text from
A3 into A1 of a new worksheet, then entered the following two formulas:


B1 = MID($A$1,ROW(),1)
C1 = CODE(MID($A$1,ROW(),1))


I copied B1 and C1 down the columns until the entire string was evaluated. The
spaces between words ("Analysis" and "Period", "Processing" and "Days") is
CHAR(32), all other spaces are CHAR(160).

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A3,CHAR(160),REPT(" ",10)),10))


Steve wrote:
Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If you like,
email me a *SMALL* sample worksheet with the error and I'll take a look at it.
Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

.

You are welcome. Glad we could finally get it working.

Steve wrote:
Thank you so very, very much. I really appreciate all your patience.
I don't think I even want to begin to know what a non-normal space is, but
the final formula works great.

Thank you again,

Steve

"Glenn" wrote:

The character after the colon is not a "normal" space. I copied the text from
A3 into A1 of a new worksheet, then entered the following two formulas:


B1 = MID($A$1,ROW(),1)
C1 = CODE(MID($A$1,ROW(),1))


I copied B1 and C1 down the columns until the entire string was evaluated. The
spaces between words ("Analysis" and "Period", "Processing" and "Days") is
CHAR(32), all other spaces are CHAR(160).

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A3,CHAR(160),REPT(" ",10)),10))


Steve wrote:
Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If you like,
email me a *SMALL* sample worksheet with the error and I'll take a look at it.
Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.

"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly into a
response to this post. Do not re-type or edit in any way.

Steve wrote:
This
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

.

I've been offline for a few days thanks to the big snow storm!

I kind of had a feeling there were some whitespace characters causing this
problem.

To the OP...

If this data is copied/pasted/imported from a website or from another
application, char(160) problems are very common.

I copy/paste/import from the web just about every day. To eliminate the
char(160) problem I use this macro by David McRitchie:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Saves a ton of time and aggravation!

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
You are welcome. Glad we could finally get it working.

Steve wrote:
Thank you so very, very much. I really appreciate all your patience.
I don't think I even want to begin to know what a non-normal space is,
but the final formula works great.

Thank you again,

Steve

"Glenn" wrote:

The character after the colon is not a "normal" space. I copied the
text from A3 into A1 of a new worksheet, then entered the following two
formulas:


B1 = MID($A$1,ROW(),1)
C1 = CODE(MID($A$1,ROW(),1))


I copied B1 and C1 down the columns until the entire string was
evaluated. The spaces between words ("Analysis" and "Period",
"Processing" and "Days") is CHAR(32), all other spaces are CHAR(160).

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A3,CHAR(160),REPT(" ",10)),10))


Steve wrote:
Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If
you like, email me a *SMALL* sample worksheet with the error and I'll
take a look at it. Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.
"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly
into a response to this post. Do not re-type or edit in any way.

Steve wrote:
This =--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

.