How to keep leading zeroes when pasting into excel file?

Question

Is there any way to not change the formatting of the column when pasting a value into the column?

---

I've found a ton of workarounds that are "after the fact"-fixes. Ones where you paste it in, format the columns as text and then add the leading zeroes afterwards etc. This is not what I want.

For context I want to be able to post GTIN-numbers into a excel document and later import it on a website. GTIN can be 8,12,13 or 14 numbers long (no way to be sure which one it is).

A number that I would want to paste is 02327718200002, but every time I do that it looks like this:

What happens is that the column that was formatted as text before the paste turns into a "general"-formatted column and is read as a number. This leads to the complete removal of the leading 0. So even if I right click my column and format it as text...the leading 0 is gone.

Answer 1

Instead of using classic ways of pasting, like CTRL + V, make sure your column is formatted as text first, then right click and select "Paste special". This will allow you to paste as text.

Answer 2

Based on comments you have posted, and assuming you have already formatted the column to Text, you could use something like...

Sub PasteSpecial()

    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
End Sub

Hit Alt+F11 to open the VBA editor then paste this inside. Head back to the developer tab in excel, click on Macros and then highlight the macro (PasteSpecial). Click options, and you can then pick to add a shortcut. The code will then paste into the active cell on the visible sheet when you hit your chosen shortcut combo

When pasting into a cell within a column formatted to anything other than text, however, it will take on the default "General" type.

NB. Use this if you want to make a Custom shortcut or if you also want to do additional manipulation. There is also a shortcut build it CTRL + ALT + V that will paste special too. Personally I find the built in shortcut clumsy to use