VBA excel paste as text

Question

I need to paste numbers as text. It is problematic due to lots of zeros in front (but I need them there). First snippet, does not work (I found it on the internet).

Worksheets("B").Range("k7:k7").PasteSpecial Format:=”Text”, 
Link:=False, DisplayAsIcon:=False

The other I recorded, but it not always work.

PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, 
SkipBlanks:=False, Transpose:=False

Could someone suggest me a better solution, please?

Solution

You need to format the cells/column to 'Text' format then paste the numbers to preserve the leading zeros. If it still doesn't work, try to paste the numbers to Notepad first and then copy all numbers in Notepad and paste it back to the cells/column now formatted as 'Text'. Hope it helps.

Comments

[Zac]

Have you tried adding an ' infront of your text in the cell? that should make excel treat your numbers as text and keep leading zeros

[Roan]

Or you can try to do as in this article, a couple of solutions: support.office.com/en-us/article/… Or try this formula: =TEXT(A1,"000-00-0000") which will give this result: 012-34-5678