Numbers Not Formatted Correctly

[aferoz]

Hi,

I'm working with an excel file which imports data from another software. For some reason, all the numbers come in with the decimal and the comma switched, so for example the cell in excel as 1.000,05 when it should be 1,000.05. Is there a way to fix this with a formula or something? I've tried changing the formatting options for the cell but that doesn't fix it.

Any help would be much appreciated.

Thanks

[Steve0492]

Hello aferoz, welcome to the forum.

Are you familiar with find and replace? (From the home tab, find the icon that looks like binoculars under Editing).

I would first find and replace all periods with blanks.
Find: "."
Replace: ""

Then I would find and replace all commas with periods:
Find: ","
Replace: "."

You can then format the numbers any way you want. Having actual commas typed into numbers will not all you to use formulas as Excel will read the number as a mix of numbers and text.

Let me know if this helps!

Steve

[Ace_XL]

Another solution..

=SUBSTITUTE(SUBSTITUTE(A1,".",",",1),",",".",2)

[aferoz]

Thanks Steve and Ace!

Both your solutions worked. I really appreciate it