Hi Freshman,

Assuming your data is in cell A12, array enter (Ctrl+Shift+Enter) the
following formula in cell B12

1*MID(A12,MATCH(TRUE,ISNUMBER(1*MID(A12,ROW($1:$18 ),1)),0),COUNT(1*MID(A12,ROW($1:$18),1))+IF(ISNUMB ER(MATCH(".",MID(A12,ROW($1:$18),1),0)),1,0))

This will give you the number only.

Hope this helps

Regards,

Ashish Mathur

"Freshman" wrote:

Dear all,

I downloaded a file from an external program in Excel format. The file
contains 6 columns with the prices at the last column. I tried to sum up the
last column but found that the figures together with other text are in
"General" format - e.g. "$450.00HKD". As such, I cannot do any calculations.
Since the last column has 600+ rows and I don't want to change the cell
format one by one. Would any experts tell me how to change the cell value to
"$450.00" in currency format?

Thanks.