Excel Number Format after exporting from Pastel Xpress

[Questor]

Hi there

If I export from Pastel Xpress to Excel, I am unable to manipulate figures as empty cells contain what looks like an apostrophe and I end up with the dreaded #VALUE error message.

I have tried Text-to-Columns, saving as CSV, and various other hacks to no avail.

Any suggestions, Clever Peeps?

Thanks in advance.

[Justloadit]

If I am not mistaken, you have to set up your local Windows to South Africa, and or the number format to D.P. not ','.
The default setting is that the decimal symbol is a comma and the list separator is a semicolon.
The decimal symbol should be a period and the list separator should be a comma.

In reference to number and currency formats.
These are found here:

Control Panel > Clock, Language and Region > Change date, time or number formats > Additional settings > Numbers > Decimal symbol
Control Panel > Clock, Language and Region > Change date, time or number formats > Additional settings > Numbers > List separator
Control Panel > Clock, Language and Region > Change date, time or number formats > Additional settings > Currency > Decimal symbol

[acheronia]

- Select the columns that need fixing
- Use the Find and Replace function (Ctrl H)
- In the Find what field type ' (apostrophe)
- Leave the Replace with field blank
- Click Replace all

If that doesn't work, insert an empty column, and then if the apostrophe value is in A1, and the empty column is B, type in B1 =value(A1)
See if this provides the correct result.
If it does, then just drag the formula down for the rest
Oh, and remember to copy the values in B and paste them again as values

[Questor]

Hi Justloadit - my Windows (10) settings are as you specified but it makes no difference.

I noticed something weird though - the numbers are not all in the same format. Some are left-justified with no separator and a comma for decimal and others are right-justified with a comma as the separator and a period for the decimal point. This for both debit and credit columns.

Moreover, the apostrophe only appears in blank cells in the debit column.

The data is exported from a very old Pastel Xpress, I believe, which might be compounding the problem. I did have a workaround for this problem as suggested by acheronia below but that doesn't work in this case.

Next step: I'll contact a Pastel/Sage reseller in Cape Town and see if he can shed some light.

Thanks for your help, though.

[Questor]

Hi acheronia - thanks for your response. As you can see from my answer to Justloadit, no luck.

Regards

[Justloadit]

Hi Justloadit - my Windows (10) settings are as you specified but it makes no difference.

I noticed something weird though - the numbers are not all in the same format. Some are left-justified with no separator and a comma for decimal and others are right-justified with a comma as the separator and a period for the decimal point. This for both debit and credit columns.

Moreover, the apostrophe only appears in blank cells in the debit column.

The data is exported from a very old Pastel Xpress, I believe, which might be compounding the problem. I did have a workaround for this problem as suggested by acheronia below but that doesn't work in this case.

Next step: I'll contact a Pastel/Sage reseller in Cape Town and see if he can shed some light.

Thanks for your help, though.

Your system has saved the file in xxx.CSV, with the new Excell, strange things occur as you have mentioned.

What I do is that I change the extension from 'CSV' to 'txt'.
Then I open Excell, go to 'File', next select 'Open', then browse to the directory I have saved the changed file.txt, you won't see it yet, because Excell defaults to looking for files with '.csv' or 'xlsx', however if you look to the right at the bottom of the file selection window, you will see a box, which says "All Excell files (".xlx",....), and so on, click this box, and a menu opens, scroll to the top where it says "All Files "*.*", click on this, and all files will now shoe in the window.
Select your file and double click, a box Text Import Wizard will show.
You will see your data in the window, you can select delimited, or fixed width. When you select it you may see the data change.
Now press the "Next" button.
A new box pops up. Under the "Delimeters box, select one of the delimiters, you will immediately see the data change. If it suits what you are looking for, then press the Next button. Excell will now open the file. You may recognise the data.
Play with the "delimeter' to see if it matches the way you want to see your data, reloading again. Save the fole in 'XLSX: format, and it will open correctly. You can also save it in "CSV' format for export.
In my case I usually select the delimeter ';', and solves my problem, I don't know if it will solve yours, but give it a try.

[Questor]

Fantastic!

Thank you, Justloadit.

And thank you for relieving me of the idea that I was sinking slowly into insanity, due entirely to an apostrophe.

[Kevin Smith]

Sorry for the late reply - based on your explanation I assume you are exporting a TB directly to excel. Pastel will keep a blank place holder apostrophe in the debit column wherever there is no value. Find and replace will not recognise it. Quickest method I found to resolve it is as follows:
Print the report to excel as you normally would.
Insert a column in A. Add a number set down all your rows (Put a 1 in cell A1, a 2 in A2 and drag down / flash fill to the bottom of your report)
Select all data and sort by largest to smallest on the debit column (assuming it is now E)
Highlight all the cells with the ' as they are now grouped together at the top of the report and press delete.
Sort by Column A again to get your data back into the original order, then delete column A.
It should be quicker than the export save convert method mentioned above.

[Questor]

Hi Kevin

Apologies for the late response to the reply.

Your method gets rid of the apostrophe but, in order to manipulate the data, I have to change the settings as noted in the earlier replies. I prefer brackets to indicate negative but it seems I'll have to settle for a minus sign.

Many thanks