How do I remove apostrophes (') in seemingly empty cells?

Hi u'all ...

Synopsis of my problem: when I export a report from Pastel to Excel, Pastel puts apostrophes as placeholders in certain cells so that the report on Excel looks like it would had you printed it out.

These apostrophes causes problems with some of my formulas. I have overcome the problem by saving the exported file as a csv and then, after reopening it, saving as a xlsx. With short files, I just copy to Word and copy back to Excel, un-wrap the text and it's done.

I have now attached the file. The 1st sheet is the catastrophe and the 2nd the various codes that I've tried. I've also copied as values to another sheet, multiplied by 1, 0 and a blank cell, amongst others I don't recall now. These work with the text and numbers prefixed with apostrophes but not the empty cells.


(Thank you to the kind person who took the time to send me a message about shortening this post. If you want to read the hooplah I wrote first, it's below.)

Thanks all

test - apostrophe.xlsx

----------------------------------------------------------------
ORIGINAL POST - VERBOSE & VERBIAGE:
I've just joined this forum, having scoured the net for days 'n days for an answer to my problem. For the moment I have overcome my problem by saving as a csv and, after reopening, saving as a xlsx.

My problem is that, when sending info from Pastel to Excel, Pastel sends apostrophes as placeholders so that the Excel sheet resembles the printout (since you are effectively "printing" to Excel instead of to a pdf or printer). As it happens, it also puts these apostrophes in front of anything that is not a number, for instance an order number which is SO245 gets one but 53642 does not. This does not really bother me. My debit and credit columns are my main problem. When there is a debit value, it's okay but when it's a credit value, the debit is a empty cell, which is not so empty.

Don't get me wrong, being able to export to Excel is awesome but then, when you have several pages of rows and columns, "ctrl click, delete" becomes a long, brainless, time consuming, mind numbing bind. I'm clearly wanting to use the figures and these "empty" cells are not empty, resulting in errors when it becomes part of a formula. You'd think MS would have done something about this by now. (When you search for the constants, these empty cells are chosen and searching for blanks they are not ... very annoying)

There are many brilliant suggestions on the internet, some from MS (e.g. Ctrl H, find ~' ... ) but this does not work for the supposedly empty cells. As a matter of fact, one solution removed the pesky apostrophe and STILL the cell was not blank until I pressed delete! Every solution I've found (+- 15) made my heart sing with joy but the songs deteriorated from full on, head banging, heavy metal to heart wrenching, wrist gnawing, break up ballads. Very very sad!

Anyhoo, the "saving as" works for the short term with both the text, numbers as well as the less than blank cells, specifically when you're dealing with huge sheets. With smaller sheets, copy/paste to Word and back also does the trick, once you "un-wrap text" things.

So, there you have it. Does anyone have any earth shattering, mind blowing solutions they can share? I'm using MS 2010 by the way and am not picky as to the elegance of the solution, whether it be macro/VBA, copy/paste, multiply this with that ... just as long as it's fast(ish) and not "ctrl clicking".

Thanking you from the deepest recesses of my ever Excel loving heart

Till later then
~T~

Try this rather small macro:

Please Login or Register  to view this content.

Suggest you post a sample workbook so that people have something to play with.

Regards, TMS

Thanks. Jakobshavn's code seems to work very effectively. I can't offer a better alternative. A global replace with a tilda (~) doesn't seem to work ... "Excel found no data", etc.

Have you tried the code?

Regards, TMS

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.