CODEWITHSUNDEEP
excelexcel-formula
Danieboy
Danieboy

Reputation: 4521

How to keep leading zeroes when pasting into excel file?

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 when I post 02327718200002 into a text-formatted column.

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.

Upvotes: 9

Views: 29079

Answers (4)

Peter O'Driscoll
Peter O'Driscoll

Reputation: 21

Excel has an easy solution now, to avoid automatic data conversion.

File, Data, Automatic data conversion, Remove leading zeros (uncheck)

Upvotes: 2

jerichaux
jerichaux

Reputation: 66

Hello from the world of "I have an incredibly niche problem at the root of which lies this issue". I've looked far and wide for a solution, and hopefully now you won't have to.

If the style mso-number-format: \@; is applied to an HTML table's <td> element (containing the issuous characters), Excel will accept the numeric formatting override and display the content as desired, allowing a number to be displayed with a leading and/or trailing zero with a decimal point.

Example (fully working and may be pasted into Excel):

<table>
  <tbody>
    <tr>
      <td style="white-space: nowrap;mso-number-format: \@;">
        011001010110110001101111.011010000110010101101100
      </td>
    </tr>
  </tbody>
</table>

Upvotes: 1

Takarii
Takarii

Reputation: 1648

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

Upvotes: 4

AsheraH
AsheraH

Reputation: 472

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.

Upvotes: 7

Related Questions