simple formula question

[brinded]

I have cells containing data from web queries that have brought in a
symbol and a price together, for example cell C10 contains:
GAM.MC,16.55
(where GAM.MC is a ticker symbol, comma separated, followed by price)
I'd like to multiply the price (after the comma) by the contents of
another say, say C11, containing number of shares.
How do I get =C10*C11 to work without displaying 'VALUE!!'

In essence I need to define C10 in my formula as only the data
following the comma.

Any ideas?

 

[Mike Middleton]

brinded -

Maybe you could use Data | Text To Columns (specifying the comma separator)
to put the symbol and number into separate columns.

- Mike
www.mikemiddleton.com

 

[Jim Gordon MVP]

Hi,

You can use Text to Columns on the Data menu to parse the symbol from
the price.

Another approach would be to use cell formulas to parse the data. You
can use the FIND function to determine what position the comma is, the
LEN function to count the total number of positions, then use LEFT and
RIGHT to split the stuff up.

GAM,37.80 | 4 | 9 | GAM | 37.80

is what you get from

GAM,37.80 | =FIND(",",A1) | =LEN(A1) | =LEFT(A1,3) | =RIGHT(A1,(C1-B1))

-Jim

 

[brinded]

Thanks both Jim and Mike,

'Text to Columns' does the trick and I've also learned a lot from the
other commands.

-regards, David