Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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) "hui" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
I wanna take A1 as a string and count how many "-" it has and find where "-"
is. May I do it? thanks "Bob Phillips" ... 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) "hui" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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) "hui" wrote in message ... I wanna take A1 as a string and count how many "-" it has and find where "-" is. May I do it? thanks "Bob Phillips" ... 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) "hui" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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 "Bob Phillips" ... 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) "hui" wrote in message ... I wanna take A1 as a string and count how many "-" it has and find where "-" is. May I do it? thanks "Bob Phillips" ... 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) "hui" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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" ... 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE about function Find
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" ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Help me find function Datedif | Excel Worksheet Functions | |||
Function to find difference between two similar worksheets is miss | Excel Discussion (Misc queries) | |||
Need Excel Function to FIND Text - Help! | Excel Worksheet Functions | |||
Can't use Find function in formula | Excel Discussion (Misc queries) |