Removing apostrophe (') character from Excel after exporting from Pastel Xpress

[Questor]

Hi

After exporting TBS from Pastel to Excel, I routinely use one-column TBs, subtracting the credit column amounts from the debit column ones.

This gives a #VALUE! error so I have to insert zeros in each apparently blank cell.

Find and Replace does not work.

Any ideas?

Thanks

[Krono9]

Not sure the exact format express exports as as i use BIC reports in partner.

An option which i used sometimes to calculation the third "single" combined column assuming the debit and credit columns are the first two columns:

=IF(A1="'",-B1,IF(A1="",-B1,A1-B1))

Should work.

[Justloadit]

It could be that Excell has converted the value into a text string, and not a number.
Try to format the cell/column (Right Click Mouse then select 'Format Cell' then select number or Currency) then try again.

[Kevin Smith]

The TB export puts a ' as a spacer in the DR column if there is a credit amount. This gives you the #VALUE when used in formulas. I haven't found a quick solution. What I do is insert a column on the left of the report and drag numbers down to the end of the report. I then select all the data and sort the report by the DR column, select all the "blank" spaces which have the ' in them and delete them. Sort again by column 1 so everything is back where it should be, then delete column 1.

[Questor]

So simple! So elegant!

Thanks Krono9

[Questor]

Thanks, Justloadit.

I talked to a BI lady who reckons it is a defined export from Pastel, rather than an Excel thing.

I'll try your solution anyway, 'cause the more solutions, the merrier, not so.

[Questor]

Thanks, Kevin.

In the spirit of acquiring knowledge I'll try your solution as well.

Very grateful for all the solutions as this problem has been irritating me for years...