Change General Format to Currency Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
If I understand you correctly, the data may have imported as text, including
the HKD suffix.
If so, Select your last column and use Edit/Replace to find instances of HKD
and replace with nothing.
Rgds,
ScottO

| 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.
 
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(ISNUMBER(MATCH(".",MID(A12,ROW($1:$18),1),0)),1,0))

This will give you the number only.

Hope this helps

Regards,

Ashish Mathur
 
Assuming the last col is col F, data from row1 down, and if the currency
code are all 3 alphas ("HKD", "SGD", etc) at the rightmost end, then this
might suffice:

Put in say, G1: =LEFT(F1,LEN(F1)-3)+0
Format G1 as currency, fill down
 
Back
Top