Breaking out data in a cell

[Robert Smith]

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

 

[omnicrondelicious]

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.

 

[Guest]

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

 

[Guest]

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

 

[omnicrondelicious]

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.

 

[Guest]

your formula fail if it is....

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

 

[Robert Smith]

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

 

[omnicrondelicious]

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.

 

[David Biddulph]

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(...