Text to number conversion with trailing -

I would like to convert 1.234,00- to -1,234.00 for calculatation and
accounting purposes.
I received the formula for the trailing -:
=IF(TRIM(RIGHT(A1,1))="-","-"&SUBSTITUTE(TRIM(A1),"-",""),TRIM(A1))
The decimal comma problem can be corrected with a long edit/replace excercise:
, to x and . to y and then replace the x and y again with the correct
decimal and comma.

Can anyone help with one formula or VBA to do this all at once? And can such
a formula cater for negative and positive numbers?

--Select the range
--Hit Ctrl+H. Find what: , (comma) Replace with: leave blank and replace all
--From menu select Data>Text to Columns>Next>Next
--From 'Convert text to columns wizard Step3 of 3' hit 'Advanced' button.
Make sure you have checked 'trailing minus for negative numbers' and hit OK.
--Hit finish

If this post helps click Yes
---------------
Jacob Skaria


"wynand" wrote:

> I would like to convert 1.234,00- to -1,234.00 for calculatation and
> accounting purposes.
> I received the formula for the trailing -:
> =IF(TRIM(RIGHT(A1,1))="-","-"&SUBSTITUTE(TRIM(A1),"-",""),TRIM(A1))
> The decimal comma problem can be corrected with a long edit/replace excercise:
> , to x and . to y and then replace the x and y again with the correct
> decimal and comma.
>
> Can anyone help with one formula or VBA to do this all at once? And can such
> a formula cater for negative and positive numbers?