Custom Number Formats only work on data entered as numbers, not data entered as Text.
The ' forces it to be text (which allows you to keep the leading zero, as leading zeroes have no meaning to numbers and are automatically dropped)/
You should be able to convert your text to numbers by selecting the column, going to Text to Columns (found on the Data menu), and clicking Finish.