Startseite » Excel EN » How to Change from Commas to Dots in Excel 💡 – The Ultimate Guide

How to Change from Commas to Dots in Excel 💡 – The Ultimate Guide

If your Excel file is using commas (“,”) as decimal separators and you need to switch to dots (“.”), you’re in the right place. This often happens when you work with international data, import CSVs, or change regional settings. In this guide, you’ll learn how to change from commas to dots in Excel—step by step, using built-in tools and keyboard shortcuts for Windows, macOS, and Ubuntu. ⚙️✅

Why You Might Need to Change Commas to Dots in Excel

Different countries use different decimal separators:

  • Comma (,) → used in most European countries (e.g., 3,14)
  • Dot (.) → used in English-speaking countries (e.g., 3.14)

This mismatch can lead to calculation errors, formatting issues, and unreadable files—especially when importing or exporting data.

✅ Changing the separator fixes:

  • Broken formulas
  • Incorrect number formatting
  • Compatibility with international users

Fastest Method: Find and Replace

The easiest way to switch commas to dots is by using the Find & Replace function.

✨ Steps:

  1. Select the range or column you want to edit.
  2. Open the Replace dialog using your system’s shortcut:
    • Windows: Ctrl + H
    • macOS: Cmd (⌘) + Shift (⇧) + H
    • Ubuntu (LibreOffice Calc): Ctrl + H
  3. In the Find what field, type a comma ,
  4. In the Replace with field, type a dot .
  5. Click Replace All

🎉 All commas are instantly converted to dots!

Use a Formula to Convert Commas to Dots

If you’d rather keep your original data intact and create a converted column, try using this Excel formula:

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

➡️ This returns the value from A1 with commas replaced by dots.

To convert the text result back into a number:

=VALUE(SUBSTITUTE(A1, ",", "."))

💡 Great for keeping raw data untouched and calculations accurate.

Change Decimal Separators via Excel Settings

Another clean method is to change Excel’s default separator:

🔧 For Windows Excel:

  1. Go to File → Options
  2. Click Advanced
  3. Uncheck “Use system separators”
  4. Set Decimal separator to .
  5. Click OK

🍏 For macOS Excel:

  1. Excel → Preferences → Advanced
  2. Under “Editing options”, uncheck “Use system separators”
  3. Change decimal to ., thousands to ,

⚠️ This method affects all new and existing Excel files.

Caution: Avoid Replacing in Text Fields

🚨 Replacing commas in text can lead to errors (e.g., “Hello, world” → “Hello. world”). Always check:

  • You only select numeric columns
  • You preview changes when possible
  • You back up your file first

Best Practices for Working with International Data

  • Always check decimal settings when importing/exporting
  • Use formulas instead of overwriting raw data
  • Create a macro for repetitive formatting tasks
  • Consider using Power Query for complex transformations

❓ Frequently Asked Questions

Will changing commas to dots break my formulas?
Not if you only change the values, not the formula logic. For formulas, use Excel’s native functions.

Can I undo a Replace All action?
Yes, just press Ctrl + Z (or Cmd + Z on Mac) to undo the last action.

Is it better to use settings or find/replace?
Find & Replace is quicker, but changing the system/Excel settings ensures consistency.

Why are my numbers still being treated as text?
After replacing, use =VALUE() to convert text numbers into actual numbers.

Do these steps work in Excel Online?
Partially. Excel Online supports find and replace, but has limited access to settings.

How can I prevent this issue in the future?
Set your regional settings or Excel preferences based on your most-used format.

✅ Final Thoughts: Change from Commas to Dots in Excel Effortlessly

Whether you’re cleaning up imported CSV files, collaborating internationally, or prepping data for analysis, knowing how to change from commas to dots in Excel is an essential skill. With keyboard shortcuts, formulas, settings, and macros, you can choose the best approach for your workflow. 🧠💼

Don’t let decimal confusion mess with your calculations—clean up your data like a pro! 📊✨

Complete List of Windows Keyboard Shortcuts

If you need help for Windows, you can find a whole list of all keyboard shortcuts here.

https://keyboard-shortcuts.org/

Advertisment