#VALUE about function Find

good point and I agree that this is totally messed up, but if you go back to
his original question all i did was make sure that he got it in the format he
was trying to enter without having to type it in again
--
Allllen


"Bob Phillips" wrote:

Call me stupid, but if you format a number to have a - as the 5th character,
won't the FIND always return 5. In addition, a number such as 12.34 will
also return 5.

Confused!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Haha....it works well, very good. thank you very much Allllen

hui


"Allllen"
...
OK try this instead then:

A B C
1 1978-9-22 =TEXT(A1,"yyyy-m-dd") =FIND("-",B1)
2

Please rate me.

--
Allllen


"hui" wrote:

when I set Column as TEXT, the content changes and look like number,
such
as
A1(1978-9-22) change 28734. may I avoid it?

thanks




"Allllen"
D¡ä¨¨????¡éD???
1) Format cell A1 as text (under Format Cells)
2) Enter 1978-9-22
3) Now your find function will work. The down side of this is that
cell
A1
is now a text and not a number (means Excel won't be able to think of
it
as a
date).

Instead of formatting cell A1 as a text, you could just type
'1978-9-22
(with the apostrophe). That has much the same effect.

If you can avoid having it as a text I would recommend leaving this
as
a
date like bob suggests because it is much more flexible like that.
Depends
what you want to do with it of course.

--
Allllen


"hui" wrote:

I set A1="1978-9-22", B1="find("-",A1)" and return, but B1 shows
#VALUE!,
why? How can I resolve it? thanks in advance.

A B C
1 1978-9-22 =find("-",A1)
2
3


hui

Yes, I am also confused by the huge and unsoted date data. I just wanna deal
with it easily as soon as I can with worksheet function. anyway excel is a
strong office assistant. thanks for your suggestion.

hui

"Bob Phillips" ...
Call me stupid, but if you format a number to have a - as the 5th
character,
won't the FIND always return 5. In addition, a number such as 12.34 will
also return 5.

Confused!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Haha....it works well, very good. thank you very much Allllen

hui


"Allllen"
...
OK try this instead then:

A B C
1 1978-9-22 =TEXT(A1,"yyyy-m-dd") =FIND("-",B1)
2

Please rate me.

--
Allllen


"hui" wrote:

when I set Column as TEXT, the content changes and look like number,
such
as
A1(1978-9-22) change 28734. may I avoid it?

thanks




"Allllen"
D¡ä¨¨????¡éD???
1) Format cell A1 as text (under Format Cells)
2) Enter 1978-9-22
3) Now your find function will work. The down side of this is that
cell
A1
is now a text and not a number (means Excel won't be able to think
of
it
as a
date).

Instead of formatting cell A1 as a text, you could just type
'1978-9-22
(with the apostrophe). That has much the same effect.

If you can avoid having it as a text I would recommend leaving this
as
a
date like bob suggests because it is much more flexible like that.
Depends
what you want to do with it of course.

--
Allllen


"hui" wrote:

I set A1="1978-9-22", B1="find("-",A1)" and return, but B1 shows
#VALUE!,
why? How can I resolve it? thanks in advance.

A B C
1 1978-9-22 =find("-",A1)
2
3


hui

I am not referring to your response Allllen, I am highlighting the points I
made earlier to the OP.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Allllen" wrote in message
...
good point and I agree that this is totally messed up, but if you go back
to
his original question all i did was make sure that he got it in the format
he
was trying to enter without having to type it in again
--
Allllen


"Bob Phillips" wrote:

Call me stupid, but if you format a number to have a - as the 5th
character,
won't the FIND always return 5. In addition, a number such as 12.34 will
also return 5.

Confused!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Haha....it works well, very good. thank you very much Allllen

hui


"Allllen"
...
OK try this instead then:

A B C
1 1978-9-22 =TEXT(A1,"yyyy-m-dd") =FIND("-",B1)
2

Please rate me.

--
Allllen


"hui" wrote:

when I set Column as TEXT, the content changes and look like
number,
such
as
A1(1978-9-22) change 28734. may I avoid it?

thanks




"Allllen"
D¡ä¨¨????¡éD???
1) Format cell A1 as text (under Format Cells)
2) Enter 1978-9-22
3) Now your find function will work. The down side of this is
that
cell
A1
is now a text and not a number (means Excel won't be able to
think of
it
as a
date).

Instead of formatting cell A1 as a text, you could just type
'1978-9-22
(with the apostrophe). That has much the same effect.

If you can avoid having it as a text I would recommend leaving
this
as
a
date like bob suggests because it is much more flexible like
that.
Depends
what you want to do with it of course.

--
Allllen


"hui" wrote:

I set A1="1978-9-22", B1="find("-",A1)" and return, but B1 shows
#VALUE!,
why? How can I resolve it? thanks in advance.

A B C
1 1978-9-22 =find("-",A1)
2
3


hui

BUT WHAT YOU HAVE NOW ACCEPTED AS A SOLUTION IS NONSENSE, IT DOES NOT SORT
ANYTHING.

All you will get is the number 5 for every row with a number.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Yes, I am also confused by the huge and unsoted date data. I just wanna
deal
with it easily as soon as I can with worksheet function. anyway excel is a
strong office assistant. thanks for your suggestion.

hui

"Bob Phillips"
...
Call me stupid, but if you format a number to have a - as the 5th
character,
won't the FIND always return 5. In addition, a number such as 12.34 will
also return 5.

Confused!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Haha....it works well, very good. thank you very much Allllen

hui


"Allllen"
...
OK try this instead then:

A B C
1 1978-9-22 =TEXT(A1,"yyyy-m-dd") =FIND("-",B1)
2

Please rate me.

--
Allllen


"hui" wrote:

when I set Column as TEXT, the content changes and look like number,
such
as
A1(1978-9-22) change 28734. may I avoid it?

thanks




"Allllen"
D¡ä¨¨????¡éD???
1) Format cell A1 as text (under Format Cells)
2) Enter 1978-9-22
3) Now your find function will work. The down side of this is
that
cell
A1
is now a text and not a number (means Excel won't be able to think
of
it
as a
date).

Instead of formatting cell A1 as a text, you could just type
'1978-9-22
(with the apostrophe). That has much the same effect.

If you can avoid having it as a text I would recommend leaving
this
as
a
date like bob suggests because it is much more flexible like that.
Depends
what you want to do with it of course.

--
Allllen


"hui" wrote:

I set A1="1978-9-22", B1="find("-",A1)" and return, but B1 shows
#VALUE!,
why? How can I resolve it? thanks in advance.

A B C
1 1978-9-22 =find("-",A1)
2
3


hui

no, I can sort the column, and deal with data with VBA.

hui

"Bob Phillips" ...
BUT WHAT YOU HAVE NOW ACCEPTED AS A SOLUTION IS NONSENSE, IT DOES NOT SORT
ANYTHING.

All you will get is the number 5 for every row with a number.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Yes, I am also confused by the huge and unsoted date data. I just wanna
deal
with it easily as soon as I can with worksheet function. anyway excel is
a
strong office assistant. thanks for your suggestion.

hui

"Bob Phillips"
...
Call me stupid, but if you format a number to have a - as the 5th
character,
won't the FIND always return 5. In addition, a number such as 12.34
will
also return 5.

Confused!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hui" wrote in message
...
Haha....it works well, very good. thank you very much Allllen

hui


"Allllen"
...
OK try this instead then:

A B C
1 1978-9-22 =TEXT(A1,"yyyy-m-dd") =FIND("-",B1)
2

Please rate me.

--
Allllen


"hui" wrote:

when I set Column as TEXT, the content changes and look like
number,
such
as
A1(1978-9-22) change 28734. may I avoid it?

thanks




"Allllen"
D¡ä¨¨????¡éD??? 1) Format cell A1 as text (under Format Cells)
2) Enter 1978-9-22
3) Now your find function will work. The down side of this is
that
cell
A1
is now a text and not a number (means Excel won't be able to
think
of
it
as a
date).

Instead of formatting cell A1 as a text, you could just type
'1978-9-22
(with the apostrophe). That has much the same effect.

If you can avoid having it as a text I would recommend leaving
this
as
a
date like bob suggests because it is much more flexible like
that.
Depends
what you want to do with it of course.

--
Allllen


"hui" wrote:

I set A1="1978-9-22", B1="find("-",A1)" and return, but B1 shows
#VALUE!,
why? How can I resolve it? thanks in advance.

A B C
1 1978-9-22 =find("-",A1)
2
3


hui