Count the cell contain a specific numbers in a range

S

Simon

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
 
C

carlo

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
 
S

Simon

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
 
C

carlo

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
¼sªF¬Ù¤¤¤s¥«©Z¬w²Ä¤@¤u·~°Ï FALSE ¼sªF¬Ù¤¤¤s¥«©Z¬w²Ä¤@¤u·~°Ï
N/A FALSE ¤¤°ê ¶l¬F½s½X:
N/A FALSE 453603245

Highly appreciated for your help
Simon







- Show quoted text -
Click to expand...

Hey Simon

Sorry, but I don't understand your Question.
Which 2nd and 3rd Value are you talking about?

Cheers Carlo
 
S

Simon

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
 
C

carlo

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








- Show quoted text -
Click to expand...

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
 
Top