I am living in Germany where the numbers are written (as currency) as such: 324,57€. This is analogous to 324.57€ in the American system. Recently I began using an app that calculates my costs in Amazon, and I am able to download reports that are very helpful to me. However, despite the fact that I can download them as csv. files, when I open the file it is already split into columns. Were this not the case and I needed to separate the columns manually, I could toggle the period and comma so that the data would appear in the German version. Unfortunately I cannot change the settings as to how the report is downloaded, so have been trying to change the data on my file after opening it. There is a way to change the Excel settings to switch everything to the US version, but this is cumbersome, as it would be a global change that would then negatively affect all of my existing files. I would love if any of you know how to toggle the period and comma within a file without having to change the base settings of Excel.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to change the comma and period for currency in an existing file (without changing Excel as a whole)
- Thread starter alancoqui
- Start date
Artik
Active Member
- Joined
- Jun 5, 2012
- Messages
- 364
DO NOT open a CSV file in a similar way to an XLS file, by double-clicking in the file manager.
Open a new, clean Excel file and use Import csv (or text) file. There you will have the option to change the temporary settings of the number separators.
Artik
Open a new, clean Excel file and use Import csv (or text) file. There you will have the option to change the temporary settings of the number separators.
Artik
Upvote
0
That didn't produce any data when I went to File>Import and then selected the file. The spreadsheet remained empty. Do you have another idea?
Upvote
0
Alex Blakenburg
MrExcel MVP
- Joined
- Feb 23, 2021
- Messages
- 9,493
- Office Version
- 365
- Platform
- Windows
Do you have this option on your Mac ?
Upvote
0
Yes, I can get to "From Text (Legacy)", not exactly how yours looks but I can get there.
Upvote
0
Alex Blakenburg
MrExcel MVP
- Joined
- Feb 23, 2021
- Messages
- 9,493
- Office Version
- 365
- Platform
- Windows
If you use that to open the file it should open the import wizard and let you specify the thousands and decimal delimiter. Can you try that ?
Upvote
0
I was able to try that, and it worked! Is there a way to also take a number set and make the negative numbers positive, or do I need to revert to using a "replace text" for that?
Upvote
0
Alex Blakenburg
MrExcel MVP
- Joined
- Feb 23, 2021
- Messages
- 9,493
- Office Version
- 365
- Platform
- Windows
I don't believe that you can invert the sign as part of the import process.
Are you trying to reverse the sign ie make positive negative and vice versa ? If yes then
• you could put -1 in a cell, ctrl+c, then paste value multiply.
• OR use a formula and just =-A2
If you want everything positive
• Use a formula =ABS(A2)
I believe your version of Excel has Power Query. You could do both the import and your conversions in Power Query.
Are you trying to reverse the sign ie make positive negative and vice versa ? If yes then
• you could put -1 in a cell, ctrl+c, then paste value multiply.
• OR use a formula and just =-A2
If you want everything positive
• Use a formula =ABS(A2)
I believe your version of Excel has Power Query. You could do both the import and your conversions in Power Query.
Upvote
0
Similar threads
- Question
