| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count the cell contain a specific numbers in a range
Hi All,
wud like to figure out the value which is start with 10 digit which present by True or False. See this:- TRUE 4420940138 *山有限公司 FALSE Hong Kong FALSE 廣東省*山市坦洲第一工*區 FALSE *國 郵政編碼: FALSE 453603245 Help!!! Simon |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the cell contain a specific numbers in a range
Hi Simon
try this formula: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9) Where your Value is in column A and then copy this formula down. hth Carlo On Jan 30, 1:30 pm, Simon wrote: Hi All, wud like to figure out the value which is start with 10 digit which present by True or False. See this:- TRUE 4420940138 $BCf;3M-8B8x;J(B FALSE Hong Kong FALSE $BW"ElJCf;3;TC3='Bh0l9)6HR?(B FALSE $BCfT"(B $BM9@/JTb{(B: FALSE 453603245 Help!!! Simon |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the cell contain a specific numbers in a range
Hi Carlo,
Tks a lot, it works great!!! But how can I get the 2nd & 3rd values if the first one return "TRUE" Figure out like this:- 4420940138 *山有限公司 TRUE 4420940138 *山有限公司 Hong Kong FALSE Hong Kong 廣東省*山市坦洲第一工*區 FALSE 廣東省*山市坦洲第一工*區 N/A FALSE *國 郵政編碼: N/A FALSE 453603245 Highly appreciated for your help Simon "carlo" wrote: Hi Simon try this formula: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9) Where your Value is in column A and then copy this formula down. hth Carlo On Jan 30, 1:30 pm, Simon wrote: Hi All, wud like to figure out the value which is start with 10 digit which present by True or False. TRUE 4420940138 *山有限公司 FALSE Hong Kong FALSE 廣東省*山市坦洲第一工*區 FALSE *國 郵政編碼: FALSE 453603245 See this:- Help!!! Simon |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the cell contain a specific numbers in a range
On Jan 30, 2:40 pm, Simon wrote: Hi Carlo, Tks a lot, it works great!!! But how can I get the 2nd & 3rd values if the first one return "TRUE" Figure out like this:- 4420940138 s**q TRUE 4420940138 s**q Hong Kong FALSE Hong Kong sF٤sZwĤ@u~ FALSE sF٤sZwĤ@u~ N/A FALSE lFsX: N/A FALSE 453603245 Highly appreciated for your help Simon "carlo" wrote: Hi Simon try this formula: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9) Where your Value is in column A and then copy this formula down. hth Carlo On Jan 30, 1:30 pm, Simon wrote: Hi All, wud like to figure out the value which is start with 10 digit which present by True or False. TRUE 4420940138 s**q FALSE Hong Kong FALSE sF٤sZwĤ@u~ FALSE lFsX: FALSE 453603245 See this:- Help!!! Simon- Hide quoted text - - Show quoted text - Hey Simon Sorry, but I don't understand your Question. Which 2nd and 3rd Value are you talking about? Cheers Carlo |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the cell contain a specific numbers in a range
Hi Carlo,
Let me figure it out in this way, column B is the formula which you present =AND(ISNUMBER(INT(LEFT(C1,10))),LEN(A1)9), but I wud like to get the result in column A1 , A2 & A3 if B1 return the value is "True", A1 must returns the value of C1, A2 must returns the value of C2 and A3 must returns the value of C3 which shown below. example:- A B C 1 Value 1 True Value 1 2 Value 2 False Value 2 3 Value 3 False Value 3 4 N/A False Value 4 5 N/A False Value 5 6 N/A False Value 6 "carlo" wrote: On Jan 30, 2:40 pm, Simon wrote: Hi Carlo, Tks a lot, it works great!!! But how can I get the 2nd & 3rd values if the first one return "TRUE" Figure out like this:- 4420940138 *山有限公司 TRUE 4420940138 *山有限公司 Hong Kong FALSE Hong Kong 廣東省*山市坦洲第一工*區 FALSE 廣東省*山市坦洲第一工*區 N/A FALSE *國 郵政編碼: N/A FALSE 453603245 Highly appreciated for your help Simon "carlo" wrote: Hi Simon try this formula: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9) Where your Value is in column A and then copy this formula down. hth Carlo On Jan 30, 1:30 pm, Simon wrote: Hi All, wud like to figure out the value which is start with 10 digit which present by True or False. TRUE 4420940138 *山有限公司 FALSE Hong Kong FALSE 廣東省*山市坦洲第一工*區 FALSE *國 郵政編碼: FALSE 453603245 See this:- Help!!! Simon- Hide quoted text - - Show quoted text - Hey Simon Sorry, but I don't understand your Question. Which 2nd and 3rd Value are you talking about? Cheers Carlo |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count the cell contain a specific numbers in a range
On Jan 30, 4:24 pm, Simon wrote:
Hi Carlo, Let me figure it out in this way, column B is the formula which you present =AND(ISNUMBER(INT(LEFT(C1,10))),LEN(A1)9), but I wud like to get the result in column A1 , A2 & A3 if B1 return the value is "True", A1 must returns the value of C1, A2 must returns the value of C2 and A3 must returns the value of C3 which shown below. example:- A B C 1 Value 1 True Value 1 2 Value 2 False Value 2 3 Value 3 False Value 3 4 N/A False Value 4 5 N/A False Value 5 6 N/A False Value 6 "carlo" wrote: On Jan 30, 2:40 pm, Simon wrote: Hi Carlo, Tks a lot, it works great!!! But how can I get the 2nd & 3rd values if the first one return "TRUE" Figure out like this:- 4420940138 s**q TRUE 4420940138 s**q Hong Kong FALSE Hong Kong sF٤sZwĤ@u~ FALSE sF٤sZwĤ@u~ N/A FALSE lFsX: N/A FALSE 453603245 Highly appreciated for your help Simon "carlo" wrote: Hi Simon try this formula: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9) Where your Value is in column A and then copy this formula down. hth Carlo On Jan 30, 1:30 pm, Simon wrote: Hi All, wud like to figure out the value which is start with 10 digit which present by True or False. TRUE 4420940138 s**q FALSE Hong Kong FALSE sF٤sZwĤ@u~ FALSE lFsX: FALSE 453603245 See this:- Help!!! Simon- Hide quoted text - - Show quoted text - Hey Simon Sorry, but I don't understand your Question. Which 2nd and 3rd Value are you talking about? Cheers Carlo- Hide quoted text - - Show quoted text - Now I am completely confused....sorry! The formula i sent you contains now two different Cells (A1 and C1): =AND(ISNUMBER(INT(LEFT(C1,10))),LEN(A1)9) What's the difference between column A and column C Why do you have values in A2 and A3 even if B2 and B3 are FALSE? Let's get things straight: Column B is my Formula which column is the original Data? (A or C) My Formula should reference only this column A: =AND(ISNUMBER(INT(LEFT(A1,10))),LEN(A1)9), C: =AND(ISNUMBER(INT(LEFT(C1,10))),LEN(C1)9), What do you want to show in the other column? (C or A) Maybe it would be the best if you use examples instead of "value1" otherwise i cannot distinguish between Column A "value1" and Column C "value1". Would love to help you Carlo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count specific colour from cell range with varied colors? | Excel Worksheet Functions | |||
| Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
| Count Specific word in specific range | Excel Worksheet Functions | |||
| Count If Specific word in specific range | Excel Discussion (Misc queries) | |||
| count by specific text color in range of cell | Excel Discussion (Misc queries) | |||
Linear Mode
