Importing text with numbers that have decimal or thousands separators

When you open a text file that contains numbers in Microsoft Excel, Excel recognizes the numerical data as such so that you can perform mathematical operations on the data. If your numbers contain decimal or thousands separators, Excel recognizes these separators as long as they are specified on the Number tab of Windows® Control Panel Regional Settings Properties dialog box.

For instance, if you specify in Control Panel that the decimal separator is a comma (","), and you have a number such as 325,45 in your text file, Excel recognizes 325,45 as numerical data and treats it as such. However, if you open a file that originated in the United States, where periods are more commonly used as decimal separators, and you have a number like 325.45 in your text file, Excel does not recognize the period as a proper separator and instead treats this data like text, which could result in inaccurate sorting or inaccurate formula results.

The solution? You could change the settings in Control Panel to match the file you're importing. But that may not be practical or convenient, and may cause problems with other files that rely on your Control Panel settings. A better solution is to specify what kind of separators to look for in a particular text file. You can do this in Excel on a file-by-file basis when you open text files by using the Text Import Wizard.

Specify separators by using the Text Import Wizard

1. On the File menu, click Open, or—if you're importing the file as a refreshable data range—point to Get External Data on the Data menu, and then click Import Text File.
2. In the Look in box, locate the drive, folder, or Internet location that contains the file you want to open.
3. In the Files of type box, click Text Files.
4. Double-click the file you want to import.
5. Follow the instructions in Steps 1 and 2 of the Text Import Wizard to specify how you want to divide the text into columns.
6. In Step 3 of the Text Import Wizard, click the Advanced button.
7. Click the arrow next to Decimal separator, and choose the separator that your text file uses for decimals (this may not necessarily be the kind of separator you want displayed). For example, if your text file uses commas to separate decimal places, click ",". Excel recognizes the comma as a decimal separator, treats the data as a number, and displays the decimal separator according to that set in Control Panel.
8. Click the arrow next to Thousands separator, and choose the separator that your text file uses for thousands. For example, if your text files uses periods to separate thousands places, click ".". Excel recognizes the period as a thousands separator, treats the data as a number, and displays the thousands separator according to that set in Control Panel.
9. Click OK.

This procedure also works when text exists in one column of a worksheet, and you use the Text to Columns command on the Data menu to break the text into separate columns.

Want to reset the settings? To change these settings so that they match Control Panel settings, click the Reset button.

More information

For more information about importing text files, type importing text in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

You can also get more information about Importing text files of 257 or more columns.