Why do numbers appear as text and there is a space between dollars and cents instead of a period.

Question

Recently upgraded to a newer computer, with Windows 11, and had Microsoft Office 2019 installed. When a spreadsheet is downloaded from Square, it downloads as a csv, all the numbers are text and left justified. If the number fields are set to numbers (or dollars) with comma and two decimal places, nothing happens to the text. If the number is re-entered, it appears with dollar sign and comma(s) and a space where the decimal mark belongs, i.e.: $123,456,789 32.

We have done some sort of Microsoft fix (with the help of a qualified IT guy) and nothing changed.

How is this problem solved? Keep in mind that our organization is a 501(c)3 and we don't have a bunch of money to throw at problems. This one just doesn't make any sense.

Donald Cox

Treasurer Emeritus

Krewe of St. Andrews, Inc.

Answer 1

The issue you are experiencing with numbers appearing as text and having a space between dollars and cents instead of a period is likely related to the list separator settings in your Windows Regional settings. When a CSV file is opened in Excel, it uses the system's list separator character, which may not match the format of the data in the CSV file. This can lead to Excel interpreting the numeric values as text, especially if the formatting does not align with the expected decimal or thousand separators.

To resolve this issue, you can try the following steps:

1. Check Windows Regional Settings: Go to the Control Panel and check the Regional settings. Ensure that the list separator is set correctly (typically a comma for US settings).
2. Excel Options: In Excel, go to File > Options > Advanced. Under the "Editing options" section, check if "Use system separators" is selected. If it is not, you may want to enable it to match the Windows settings.
3. Re-import the CSV: After adjusting the settings, try re-importing the CSV file into Excel. You can also use the Text Import Wizard to specify the correct delimiters and formats during the import process.
4. Convert Text to Numbers: If the numbers are still left justified and treated as text, you can convert them back to numbers by using the VALUE function or by multiplying the text values by 1.

These steps should help in resolving the formatting issues with your CSV data in Excel.

---

References:

• Formula errors in Excel when list separator isn't set correctly

Answer 2

The problem was in the regional settings.