View original (French)

Convert US numbers to French numbers in Excel

Solved
faudouy -  
 faudouy -
Hello,
I want to convert numbers imported by copy-paste (no other solution) into a currency format.
The problem lies with this type of number: 1,762 which should display as 1 762€.
When a number is in the form 762, it should display as 762€.
In short, numbers from my import that have a comma should be multiplied by 1000.
I’ve searched in custom formats and with conversion, but I can’t find a suitable solution.
Thank you in advance for your help.
Configuration: Windows 7 / Internet Explorer 8.0

6 answers

  1.
    Mister Z Posted messages 39 Status Member 22
     
    Try to make a "special paste". You should be able to remove the commas in your paste settings.
    Then, before pasting your data, in the relevant cells, do this:
    right-click -> "Cell Format" -> "Number" tab -> Choose "Currency" making sure it's in € and 2 decimal places.
    3
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    In that case, you need to change the default thousand separators and decimal separator.
    You can do this for all of Windows in the control panel ('Region and Language / Additional settings...' on Windows 7).
    Or temporarily for Excel (before importing) in 'Tools / Options...' tab 'International' (and you can then recheck 'Use system separators,' those defined in the control panel)

    Eric
    1
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Bonjour

    Try this parameterized macro

    Sub translate(cell) separ = Split(cell, ",") With cell .Value = separ(0) & separ(1) .NumberFormat = "#,##0 $" End With End Sub Sub test() translate (Cells(1, 1)) End Sub


    To adapt according to the layout of your "us" sheet: loop for example if possible

    If, for example, US number from C2:C100

    sub adapt()
    for row=2 to 100
    translate(cells(row,"C"))
    next

    --
    Michel
    1
  4. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    replace , (comma) with nothing (empty) in your selection.

    eric
    0
    1. faudouy
       
      Hello and thank you; it’s not that simple.
      I also retrieve numbers like 8,000, in which case I want to get 8000; when I copy this number 8,000, it pastes it like this: 8
      Special pastes don’t offer me any solution. I also can't work with the import of a txt file, I'm at an impasse.
      Yet, this file is an Excel workbook made in the USA!
      Thank you for your contributions.
      0
  6. faudouy
     
    Hello
    in the paste options, I don't have the option to remove the commas.
    I have:
    Word document object
    Image
    HTML
    Unicode text
    Text

    That's all I have!
    Sorry
    0
  7. faudouy
     
    Thank you all, the solution is indeed in the Excel options to change the thousand separator (comma instead of space) before pasting, and after pasting to revert it back to the original format!
    Wishing you all the best!
    0