#VALUE about function Find

  • Thread starter Thread starter hui
  • Start date Start date
H

hui

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
 
That is because the cell holds a number, it is just formatted as you show
it, the - is not real.

If you want the year, just use

=YEAR(A1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
That is because the cell holds a number, it is just formatted as you show
it, the - is not real.

If you want the year, just use

=YEAR(A1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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.
 
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.
 
I wanna take A1 as a string and count how many "-" it has and find where "-"
is. May I do it?

thanks
 
I wanna take A1 as a string and count how many "-" it has and find where "-"
is. May I do it?

thanks
 
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
 
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
 
As I said, there are no "-" in there, it is just formatted to look that way.
Why do you want to count them?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
As I said, there are no "-" in there, it is just formatted to look that way.
Why do you want to count them?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
In Column A there are lots of unformated date data, some like 1978-9-22,
some like 1978-9, some like 1978.9.22, some like 78.9.22 or even 78.9

I wanna know what kind of date data in Column A by Function find and deal
with it.

hui
 
In Column A there are lots of unformated date data, some like 1978-9-22,
some like 1978-9, some like 1978.9.22, some like 78.9.22 or even 78.9

I wanna know what kind of date data in Column A by Function find and deal
with it.

hui
 
OK try this instead then:

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

Please rate me.
 
OK try this instead then:

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

Please rate me.
 
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)
 
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)
 
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
 
Back
Top