Change General Format to Currency Format:figures together

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

"Freshman" <[email protected]> wrote in message
news:[email protected]...
| 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

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Freshman" <[email protected]> wrote in message
news:[email protected]...
> 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

"Freshman" <[email protected]> wrote in message
news:[email protected]...
| 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

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Freshman" <[email protected]> wrote in message
news:[email protected]...
> 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.

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

"Freshman" <[email protected]> wrote in message
news:[email protected]...
| 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

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Freshman" <[email protected]> wrote in message
news:[email protected]...
> 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.