How do I COUNT the output of the LEFT function?

[Yasir]

Hi:
One of the columns contains a number of values including the text string "1/2A". I am using the LEFT function to return only the "1/2" part of the string. E.g:

=LEFT(A10, 3)

I am using the LEFT function because all the values in the column have a letter after the "1/2" and a nested IF or Count IF statement is very inelegant. I would like my function to scan a column range and return a count value using the LEFT function to seperate the "1/2" from the full string (be it "1/2A" or "1/2B" etc. Any ideas?

 

[Aladin Akyurek]

On 2002-11-07 06:18, Yasir wrote:
Hi:
One of the columns contains a number of values including the text string "1/2A". I am using the LEFT function to return only the "1/2" part of the string. E.g:

=LEFT(A10, 3)

I am using the LEFT function because all the values in the column have a letter after the "1/2" and a nested IF or Count IF statement is very inelegant. I would like my function to scan a column range and return a count value using the LEFT function to seperate the "1/2" from the full string (be it "1/2A" or "1/2B" etc. Any ideas?

=SUMPRODUCT(ISNUMBER(LEFT(A10:A20,3)+0)+0)

would count entries whose first three chars together can be interpreted as number.

Is this what you're looking for?

 

[Yasir]

On 2002-11-07 06:51, Aladin Akyurek wrote:

On 2002-11-07 06:18, Yasir wrote:
Hi:
One of the columns contains a number of values including the text string "1/2A". I am using the LEFT function to return only the "1/2" part of the string. E.g:

=LEFT(A10, 3)

I am using the LEFT function because all the values in the column have a letter after the "1/2" and a nested IF or Count IF statement is very inelegant. I would like my function to scan a column range and return a count value using the LEFT function to seperate the "1/2" from the full string (be it "1/2A" or "1/2B" etc. Any ideas?

=SUMPRODUCT(ISNUMBER(LEFT(A10:A20,3)+0)+0)

would count entries whose first three chars together can be interpreted as number.

Is this what you're looking for?

Aladin you are a star! Thanks so much for your help, it worked perfectly. I'm now going to check what SUMPRODUCT and ISNUMBER functions are, as I've never come across them before. Thanks again.

Yasir

 

[Yasir]

On 2002-11-07 06:51, Aladin Akyurek wrote:


=SUMPRODUCT(ISNUMBER(LEFT(A10:A20,3)+0)+0)

would count entries whose first three chars together can be interpreted as number.

Is this what you're looking for?

One other question if anybody knows. How would you adapt the function if the column being scanned contains other values which do not begin with the text string "1/2". For example there may be 2/3, 3/4, 1/8 etc? Is there a way to:
a) Scan the column for only the partial text string "1/2"
b) Return the value as a number
c) Count the values

All help greatly appreciated.

Thanks,
Yasir

 

[Aladin Akyurek]

On 2002-11-07 06:56, Yasir wrote:
[...] I'm now going to check what SUMPRODUCT and ISNUMBER functions are, as I've never come across them before. [...]

See for additional info on SumProduct...

http://www.mrexcel.com/wwwboard/messages/8961.html

 

[Aladin Akyurek]

On 2002-11-07 07:29, Yasir wrote:

On 2002-11-07 06:51, Aladin Akyurek wrote:


=SUMPRODUCT(ISNUMBER(LEFT(A10:A20,3)+0)+0)

would count entries whose first three chars together can be interpreted as number.

Is this what you're looking for?

One other question if anybody knows. How would you adapt the function if the column being scanned contains other values which do not begin with the text string "1/2". For example there may be 2/3, 3/4, 1/8 etc? Is there a way to:
a) Scan the column for only the partial text string "1/2"
b) Return the value as a number
c) Count the values

All help greatly appreciated.

Thanks,
Yasir

Not sure I understood your questions as intended, but try...

Let A10:A20 be the range of interest.

We are only interested in the number part of the entries in A10:A20.

In B10 enter & copy down:

=LEFT(A10,3)

In C10 enter & copy down:

=IF(ISNUMBER(SEARCH("/",A10)),EVAL(LEFT(A10,3)),"")

EVAL is part of the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html


See...

aaCondCountQ Yasir.xls
	A	B	C	D	E	F
9
10	1/2a	1/2	1/2		1/3
11	1/3b	1/3	1/3		1
12	x	x
13	2/3d	2/3	2/3		E10isformatted
14	2/5e	2/5	2/5		astextbefore
15	2/3a	2/3	2/3		entering1/3
16	y	y
17	10	10
18
19
20	2/3f	2/3	2/3
21
Sheet1

This message was edited by Aladin Akyurek on 2002-11-07 09:34