Text to number conversion with trailing -

  • Thread starter Thread starter wynand
  • Start date Start date
W

wynand

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
 
Hi,

Try the formula,

=IF(ISNUMBER(FIND("-",A1)),"-","")&TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".","x"),",","."),"x",","),"-",""))

Please click "Yes" if this is helpful.

Regards,
B. R. Ramachandran
 
Back
Top