Results 1 to 7 of 7

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

  1. #1
    Full Member
    Join Date
    Nov 2017
    Location
    Robertson, Western Cape
    Posts
    27
    Thanks
    1
    Thanked 1 Time in 1 Post

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

    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

  2. #2
    Full Member
    Join Date
    Dec 2016
    Location
    Cape Town
    Posts
    29
    Thanks
    0
    Thanked 4 Times in 4 Posts
    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.

  3. Thanks given for this post:

    Justloadit (20-Aug-20)

  4. #3
    Diamond Member Justloadit's Avatar
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    2,914
    Thanks
    108
    Thanked 593 Times in 503 Posts
    Blog Entries
    1
    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.
    Victor - Knowledge is a blessing or a curse, your current circumstances make you decide!
    Solar and LED lighting solutions - www.microsolve.co.za

  5. #4
    Silver Member
    Join Date
    Aug 2018
    Location
    Johannesburg
    Posts
    221
    Thanks
    0
    Thanked 33 Times in 29 Posts
    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.

  6. #5
    Full Member
    Join Date
    Nov 2017
    Location
    Robertson, Western Cape
    Posts
    27
    Thanks
    1
    Thanked 1 Time in 1 Post
    So simple! So elegant!

    Thanks Krono9

  7. #6
    Full Member
    Join Date
    Nov 2017
    Location
    Robertson, Western Cape
    Posts
    27
    Thanks
    1
    Thanked 1 Time in 1 Post
    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.

  8. #7
    Full Member
    Join Date
    Nov 2017
    Location
    Robertson, Western Cape
    Posts
    27
    Thanks
    1
    Thanked 1 Time in 1 Post
    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...

Similar Threads

  1. [Question] Pastel Xpress V14 not exporting all customers
    By michael1986 in forum Accounting Forum
    Replies: 2
    Last Post: 27-Aug-20, 08:52 AM
  2. Exporting out of Pastel Xpress 12
    By newbie101 in forum Accounting Forum
    Replies: 0
    Last Post: 10-Dec-14, 09:28 AM
  3. [Question] Exporting Xpress V11 to Excel 2013 Win8
    By JohnKeevy in forum Accounting Forum
    Replies: 0
    Last Post: 31-Oct-14, 10:29 AM
  4. Exporting from Pastel V12 to excel
    By Tat in forum Accounting Forum
    Replies: 2
    Last Post: 18-Feb-14, 08:26 AM
  5. [Question] exporting data in pastel to excel
    By bernardshap in forum Accounting Forum
    Replies: 5
    Last Post: 28-Aug-12, 01:19 PM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •