Hi Oli,

So I was wondering if there was any other way of copy pasting the actual
displayed values and retaining my leading zeros created by the custom format
WITHOUT having to use the custom format on that column too?

If you don't mind running a macro that changes all entries permanently to text:

Sub Convert2Text()
Dim oCell As Range
For Each oCell In Selection
oCell.Value = "'" & oCell.Text
oCell.NumberFormat = "@"
Next
End Sub

Paste this in a normal module, select your column and run the macro.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com