Change French Canadian to US Dollars using VBA

[mnh]

Hello - we frequently spreadsheets completed in Quebec - so it's written in French Canadia. I'm having issues changing the currency from 55,00 $ and 1 555,00 $ to $55.00 and $1,555.00. I am not having any luck changing this to US dollars.

TARIFSÀLA JOURNÉE

59,00 $

59,00 $

60,00 $

62,00 $

Here is the code:

Union(searchRange, searchRange3).NumberFormat = "$#,##0.00"

Any help would be greatly appreciated! I'm using Excel 365 - Desktop.

 

[Joe4]

If you do a Find/Replace, replacing the " $" (note the space before the "$") with nothing, it should convert all your entries to valid numbers.
Then, since it is a number, you can apply any formatting you want to it, to show the number of decimal places you want.

 

[Cubist]

Then, since it is a number, you can apply any formatting you want to it, to show the number of decimal places you want.

The commas might be an issue if the regional thousand separators are periods. Unless I'm missing something.

@mnh What are your regional settings for the decimal separators and the thousands-separators?

 

[mnh]

When I remove the space and $ sign, it still isn't showing as a number. I checked and my regional settings are for US English - with decimal separator as a period, and thousands separator as a comma.

 

[Joe4]

When I remove the space and $ sign, it still isn't showing as a number. I checked and my regional settings are for US English - with decimal separator as a period, and thousands separator as a comma.

Can you show us what it looks like after that?

 

[mnh]

Sure - you can see when i do IsNumber - it's still showing false.

 

[Cubist]

Sure - you can see when i do IsNumber - it's still showing false.
View attachment 113986

The current format does not adhere to your current regional setting.
Try do another find "," replace with "."
Then again, find " " replace with ","

 

[Joe4]

Yeah, what cubist said.
The issue is that while your regional setting are correct, the data coming over is not in that English format!
But if you do what Cubist said, it should fix it.

 

[mnh]

alright- that sounds good! thank you so much! thank you for your help everyone!

 

[Joe4]

Glad we were able to help!

Note that you can automate all these steps in VBA code.
If you turn on the Macro Recorder and record yourself doing these steps manually, you should have most of the VBA code you need.