Hi folks,

I download a lot of stats via web queries. One set of stats includes 100 or
so cells in a single column with random data like "74th of 9354" without the
quotes. Can someone help me with a formula to break out the separate numbers
74 & 9354 in the columns to the right, so that I can average those 100 or so
cells?

Thanks for any help you can provide.

Bob

Howdy,

I reckon you could use

=LEFT(A1,FIND("of",A1)-4)

to get the first set of digits and

=MID(A1,FIND("of",A1)+3,100)

to get the latter chunk, where A1 is the cell with the data you want
to parse.

cheers,
..o.


On Sep 10, 3:43 pm, "Robert Smith"
wrote:
Hi folks,

I download a lot of stats via web queries. One set of stats includes 100 or
so cells in a single column with random data like "74th of 9354" without the
quotes. Can someone help me with a formula to break out the separate numbers
74 & 9354 in the columns to the right, so that I can average those 100 or so
cells?

Thanks for any help you can provide.

Bob

with 74th of 9354 in A1,

=LEFT(SUBSTITUTE(A1,"th of ","?"),FIND("?",SUBSTITUTE(A1,"th of ","?"),1)-1)
displays 74

and
=RIGHT(SUBSTITUTE(A1,"th of ","?"),LEN(SUBSTITUTE(A1,"th of
","?"))-FIND("?",SUBSTITUTE(A1,"th of ","?"),1))
displays 9354
--
Gary''s Student - gsnu2007


"Robert Smith" wrote:

Hi folks,

I download a lot of stats via web queries. One set of stats includes 100 or
so cells in a single column with random data like "74th of 9354" without the
quotes. Can someone help me with a formula to break out the separate numbers
74 & 9354 in the columns to the right, so that I can average those 100 or so
cells?

Thanks for any help you can provide.

Bob

I think that would work for most numbers but not all - there might be
"st of" or "nd of" or "rd of" cases too.

..o.


On Sep 10, 4:20 pm, Gary''s Student
wrote:
with 74th of 9354 in A1,

=LEFT(SUBSTITUTE(A1,"th of ","?"),FIND("?",SUBSTITUTE(A1,"th of ","?"),1)-1)
displays 74

and
=RIGHT(SUBSTITUTE(A1,"th of ","?"),LEN(SUBSTITUTE(A1,"th of
","?"))-FIND("?",SUBSTITUTE(A1,"th of ","?"),1))
displays 9354
--
Gary''s Student - gsnu2007

your formula fail if it is....

23rd of 9354
or
31st of 9354
or
22nd of 9999
and so on....

"Gary''s Student" wrote:

with 74th of 9354 in A1,

=LEFT(SUBSTITUTE(A1,"th of ","?"),FIND("?",SUBSTITUTE(A1,"th of ","?"),1)-1)
displays 74

and
=RIGHT(SUBSTITUTE(A1,"th of ","?"),LEN(SUBSTITUTE(A1,"th of
","?"))-FIND("?",SUBSTITUTE(A1,"th of ","?"),1))
displays 9354
--
Gary''s Student - gsnu2007


"Robert Smith" wrote:

Hi folks,

I download a lot of stats via web queries. One set of stats includes 100 or
so cells in a single column with random data like "74th of 9354" without the
quotes. Can someone help me with a formula to break out the separate numbers
74 & 9354 in the columns to the right, so that I can average those 100 or so
cells?

Thanks for any help you can provide.

Bob

In B1: =LEFT(A1,FIND(" ",A1)-3)+0
In C1: =MID(A1,FIND("^",SUBSTITUTE(A1," ","^",2))+1,LEN(A1))+0

select B1 to C1 and copy down as far as needed


"Robert Smith" wrote:

Hi folks,

I download a lot of stats via web queries. One set of stats includes 100 or
so cells in a single column with random data like "74th of 9354" without the
quotes. Can someone help me with a formula to break out the separate numbers
74 & 9354 in the columns to the right, so that I can average those 100 or so
cells?

Thanks for any help you can provide.

Bob

I'd like to thank everyone for their reply. I tried the first option by
Omnicron, which showed the numbers correctly, but when doing an average on
those numbers, brought up a big fat 0 for each column.

Teethless mama's formulas renders the correct number via an average formula.

As always folks, thanks for the help.

Bob

"Teethless mama" wrote in message
...
In B1: =LEFT(A1,FIND(" ",A1)-3)+0
In C1: =MID(A1,FIND("^",SUBSTITUTE(A1," ","^",2))+1,LEN(A1))+0

select B1 to C1 and copy down as far as needed


"Robert Smith" wrote:

Hi folks,

I download a lot of stats via web queries. One set of stats includes 100
or
so cells in a single column with random data like "74th of 9354" without
the
quotes. Can someone help me with a formula to break out the separate
numbers
74 & 9354 in the columns to the right, so that I can average those 100 or
so
cells?

Thanks for any help you can provide.

Bob

Whups, my bad. The "+0" that Teethless Mama remembered to add to the
end of her formula conveniently converts the value from a string to a
numeric, so that averages and other mathematical functions work.

cheers,
..o.


On Sep 10, 4:36 pm, "Robert Smith"
wrote:
I'd like to thank everyone for their reply. I tried the first option by
Omnicron, which showed the numbers correctly, but when doing an average on
those numbers, brought up a big fat 0 for each column.

Teethless mama's formulas renders the correct number via an average formula.

As always folks, thanks for the help.

Bob

The reason why you got zero from Omnicron's formula is that LEFT and MID
return text strings, so to convert to numbers you need to use a trick like
Teethless mama's +0 (or *1) or otherwise precede the LEFT or MID with a
double unary minus --LEFT(... or --MID(...
--
David Biddulph

"Robert Smith" wrote in message
...
I'd like to thank everyone for their reply. I tried the first option by
Omnicron, which showed the numbers correctly, but when doing an average on
those numbers, brought up a big fat 0 for each column.

Teethless mama's formulas renders the correct number via an average
formula.

As always folks, thanks for the help.

Bob

"Teethless mama" wrote in
message ...
In B1: =LEFT(A1,FIND(" ",A1)-3)+0
In C1: =MID(A1,FIND("^",SUBSTITUTE(A1," ","^",2))+1,LEN(A1))+0

select B1 to C1 and copy down as far as needed


"Robert Smith" wrote:

Hi folks,

I download a lot of stats via web queries. One set of stats includes 100
or
so cells in a single column with random data like "74th of 9354" without
the
quotes. Can someone help me with a formula to break out the separate
numbers
74 & 9354 in the columns to the right, so that I can average those 100
or so
cells?

Thanks for any help you can provide.

Bob