I need to sum some cells that contain both text and numbers. The numbers are
prices and the text are the products sold.
Better explanation. Columns I through N are the Transactions for the day.
The prices and text are usually different, and some are blank.
Example Cell I10 is the first sale of the day. would contain $9.99 - 80mm
Fan, J10 would be $60.00 - I.H.S. and the rest of the cells K10 -N10 would be
blank, but the next day all the cells would be full, I11 - N11 each with
different values and text..
All of them do have a price $xx.xx and a - after the price but before the
text. I need to filter the prices from the cells to add them up. I found one
thing so far that sort of works, but it's crazy long
"=SUM(--MID(TRIM(I159),FIND("$",TRIM(I159)),FIND("-",TRIM(I159))-FIND("$",TRIM(I159))))"
The problem with this is I need to add that entire line for each cell, and
it gives an error if there is a blank cell.
Can anyone help me with this, maybe a smaller formula, or atleast something
that won't give errors if there is a blank cell?

Much appreciation to all who help.

Please don't take offense, but you would be SO much better off in the long
run if you separated your numbers and your descriptions into two different
columns. Then all of the math could be with straight-forward normal
formulas.....and don't type in the dollar sign, let Excel's formatting do it
for you........

hth
Vaya con Dios,
Chuck, CABGx3



"chrisjwhite24" wrote:

I need to sum some cells that contain both text and numbers. The numbers are
prices and the text are the products sold.
Better explanation. Columns I through N are the Transactions for the day.
The prices and text are usually different, and some are blank.
Example Cell I10 is the first sale of the day. would contain $9.99 - 80mm
Fan, J10 would be $60.00 - I.H.S. and the rest of the cells K10 -N10 would be
blank, but the next day all the cells would be full, I11 - N11 each with
different values and text..
All of them do have a price $xx.xx and a - after the price but before the
text. I need to filter the prices from the cells to add them up. I found one
thing so far that sort of works, but it's crazy long
"=SUM(--MID(TRIM(I159),FIND("$",TRIM(I159)),FIND("-",TRIM(I159))-FIND("$",TRIM(I159))))"
The problem with this is I need to add that entire line for each cell, and
it gives an error if there is a blank cell.
Can anyone help me with this, maybe a smaller formula, or atleast something
that won't give errors if there is a blank cell?

Much appreciation to all who help.