Trailing Minus For Negatives in CSV vs TXT Files

You can control TXT files much better

Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.

When Excel imports CSV files it uses the default settings and in most cases the trailing negatives are imported as text, not numbers.

To control exactly how the data is imported it is best import as a .txt file.

You can convert .csv files to .txt files, but you may need to tweak a Windows Explorer setting to make the process easier.

In Windows Explorer click the Tools menu item and Folder options.

Then in the View tab uncheck the “Hide extensions for known file types” and then click Apply.

Now back in Windows Explorer you can simply click on any CSV file and change the extension from .csv to .txt to convert it into a txt file.

When you import the .txt file the minus sign should be treated correctly.

If it doesn’t, then you will need to tweak a Text Import Wizard Advanced setting.

In step 3 of the Text Import Wizard there is an Advanced button – click it.

The checkbox to fix trailing minus sign is there – make sure it is ticked and click OK.

Then click Finish in the Text Import Wizard dialog.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.