fixed : hashtags making text unreadable in Excel

Question :

Somebody sent me an Excel spreadsheet in which some of the data is unreadable since it has been replaced by text consisting of hashtag signs only…

I’ve seen this happening before, and the remedy used to be just to broaden the column, since all number that are too large to fit their cell width are automatically made unreadable…

However, in this case, if I broaden the column width, some of the hashtag text will turned into readable numbers, but other hashtag text will remain, no matter how wide I make the column…

What is wrong ?

And how can I fix this ?

 

Answer :

This seems to be a problem with Excel for Windows files that are opened in Excel for Mac (possibly more specifically with data copied or converted from another application into Excel on a Windows-PC). So PC-users will not be warned on forehand that their Excel output is not suited for use on Macs…

Fixing it turns out to be rather simple (but annoying) :

• open the file in Excel for Mac

• select the cells that are effected (or preferably even the entire column), then go to “Format” in the top menu bar

• from the dropdown-menu that appears, select “Cells…” and a window entitled “Format Cells” will open

• make sure the “Number”-tab is selected and make sure that you select “Number” or “General” from the list (make sure that it is something else than what was previously selected ; anything seems to be okay, as long as it is not “Accounting”)

• no return to your spreadsheet to check if the hashtag texts have disappeared (if not : try again)

That’s it !

Enjoy !

😉

Donate Button (MacManusNL)

Advertisement

fixed : Reduce File Size makes PDF unreadable

Question :

I have created a PDF of 4 pages in the Preview app, but I can’t eMail it to anyone because it turns out to be a 39MB file…

I know the probable cause, because some of the pages are entire A4-size pictures scanned as JPEG at 300dpi…

So I tried to use various ways of saving (or exporting) as PDF from within Preview, but they all end up with the same oversized 39MB file… except for one : the “Reduced File Size” filter from the “Export” option gives me a 240kB file that turns out to be completely unreadable…

Isn’t there any simple way to get a readable PDF of about 5MB or so ?

 

Answer :

Yes there is, but unfortunately it’s rather tricky to set up at first (since there is still no option in Preview, nor in “Save as PDF”, to adjust the PDF-output on the fly… and on top of that : there is a bug in the latest versions of OSX that prevents self-made Quartz-filters from ColorSync Utility to be accessible throughout OSX…)

Here’s how to :

1- open the ColorSync Utility that is in the Utilities folder within the Applications folder

2- if no window opens, click on File –> New Window

3- then click on the “Filters” tab

4- then click on the “+”-button in the bottom-left corner to create a new Quartz-filter

5- type “Reduce File Size 75dpi”, then click on the small round button (with the small white triangle in it) at the end of the line

6- from the pulldown menu select “Add Image Effects Component > Color Image Sampling” and now some adjusting options appear, leave most of them as-is, only at “Resolution:” type “75 pixels/inch” (so leave the other options empty and leave “Quality:” at “High”)

7- repeat this last step, but this time select “Add Image Effects Component > Grey Image Sampling” from the pulldown menu (so set “Resolution:” to “75 pixels/inch” here also)

8- tripple-check to make sure that the name of the Quartz-filter is “Reduce File Size 75dpi” and the “Resolution:” is set to “75 dpi/inch” at both instances… if that’s not the case : correct it before continuing (just retype at the correct spot)

OPTIONAL : click on the “+”-button again to create another new Quartz-filter, call it “Reduce File Size 120dpi” and repeat the above procedure, just set “Resolution:” to “120 dpi/inch” this time (…and you can even repeat this for other resolutions like 100 dpi/inch)

9- now close ColorSync Utility

10- now, in the Finder, press the ALT-key on your keyboard and select “Go” from the upper menu bar, in the pulldown and extra (ghost) folder called “Library” will appear, click on it and in the Finder window that opens, select the “Filters” folder (there you will see the Quartz-filters you’ve just created in ColorSync Utility)

11- then, in the Finder open another (new) Finder window and open the (regular) “Filters” folder that is in the “Library” folder in the “System” folder also (there you will see all the standard Quartz-filters available throughout OSX)

12- put both of these Finder windows side-by-side, then select the Quartz-filter files you’ve custom created, and drag them into the other “Library” folder (the one in the “System” folder)

13- then you get an authentication warning message that you need administrator rights to add anything into the folder, so type your administrator password and the custom Quartz-filter files will be moved into the System’s “Filters” folder

UPDATE : from OSX 10.11 El_Capitan on, you will see a grey NoEntry-sign when trying to access the System’s “Filters” folder ; this is due to El-Capitan’s SIP (System Integrity Protection) feature ; to solve this follow these extra steps :

  • instead of moving your self-made Quartz-filters into the ~/System/Library/Filters folder, you should move them to the ~/Library/Filters folder
  • since that last folder doesn’t exist by default, you will have to create it first
  • to do so, go to ~/Library in a Finder-window and click on the Sprocket-icon (a.k.a. GearWheel-icon) and select “New Folder” from the pulldown list
  • when prompted, enter you administrator’s name & password to allow this new folder to be created
  • when done, move your self-made Quartz-filters info this newly created folder at ~/Library/Filters

14- close all open windows

15- select the PDF file that’s too big and open it in the Preview app

16- in the Preview app, go to “File” and from the pulldown menu, select “Export” (do not select “Export to PDF” or “Save” or “Print” !!!)

17- in the window that appears, choose a (new) file name and a location to save the size-reduced PDF-file, then set “Format:” to “PDF” and at “Quartz Filter:” select “Reduce File Size 75dpi” and click the “Save”-button

18- then go to the Finder and select the newly created size-reduced PDF-file and right-click and select “Get Info” (or use the CMD+I key-combo), in the window that opens, check the file size (note : any file up to about 5MB can easily be sent as an eMail attachment)

19- then open the size-reduced PDF in the Preview app and check if the text is still readable… if  all text has turned fuzzy, do a new Export from the original PDF but choose another Quartz-filter (like 100dpi or 120dpi… if you didn’t create those, redo the entire process to create them and make them available throughout OSX)

20- that’s it ! you now have created a very handy and quick PDF size reduction option within the Preview app !

Enjoy !

😉

Donate Button (MacManusNL)

fixed : this PDF will not print correctly

Question :

I’ve received an invoice from energy-provider Essent in PDF. In OSX’s Preview the PDF displays perfectly on-screen, but when printed, part of the text is messed up into unreadable text. I’ve called the Essent Help-desk and they told me that this is a Mac-problem that cannot be fixed…

…is that true and isn’t there any solution for this ?

Answer :

The Essent Helpdesk is wrong (I’m sorry…). The problem is that a non-standard font is used in the invoice for which a license of €500 is needed… (this actually is the FrescoSans font family…)

Even though Preview can’t properly print this font, Adobe Reader will…

So, download Adobe Reader (if you haven’t already done so) and open and print the invoice from there.

Simple as that !

Donate Button (all)