How to keep leading zeroes when pasting into excel file?

Asked 3/1, 2017 at 12:51 Answered 26/3 at 5:0

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.

Uvea answered 3/1, 2017 at 12:51 Comment(8)

#2262287 – Felting 3/1, 2017 at 12:54

Are you pasting from a cell to a cell or from an external source to a cell ?? – Revive 3/1, 2017 at 12:54

@Gary'sStudent Well in this specific case it would be from an external source (website). But I would like to find some kind of solution where it wouldn't matter from where if possible. – Uvea 3/1, 2017 at 12:55

@PankajJaju I specificly said that this was not what I wanted. Did you even read the thread? – Uvea 3/1, 2017 at 12:56

I read that and pointed to the thread with a detailed discussion on the options available in Excel. Perhaps you should read that thread too. – Felting 3/1, 2017 at 12:57

@PankajJaju I read that thread and every answer before posting this question and it's all non-applicable to my specific problem. – Uvea 3/1, 2017 at 12:59

@Uvea - ZygD suggested a method which is very relevant to your question. – Felting 3/1, 2017 at 13:3

@PankajJaju I'm not doing an import from another file. – Uvea 3/1, 2017 at 13:5

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

Choreodrama answered 3/1, 2017 at 13:10 Comment(5)

Sorry if I sound like a beginner now but...where exactly do I put this code? I would assume it's in the Alt+F11 menu somewhere. – Uvea 3/1, 2017 at 13:14

Took me a while to find the developer tab in my Swedish version...but the solution works! – Uvea 3/1, 2017 at 13:29

It appears that sometimes it does paste in the "next" (to the right) column for some reason. Any idea? – Uvea 16/1, 2017 at 14:2

@Uvea Cant explain that. Could have something to do with your chosen keybind as the code shouldn't do that at all. – Choreodrama 16/1, 2017 at 14:7

It just occurred to me that you could add another line in the macro to first set the cell's format to Text, before the existing line that does the pasting, couldn't you? – Rebarbative 23/1 at 19:35

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.

Apostles answered 3/1, 2017 at 12:59 Comment(14)

This doesn't work. I tested it - copy the number in the question and paste special > text and you'll find the zero has been removed – Pelops 3/1, 2017 at 13:1

This did not work in any of the given alternatives (HTML, Unicode-text or text). EDIT: Changed my mind, if it's formatted as text when I paste as special - it does work. – Uvea 3/1, 2017 at 13:1

Just make sure your entire column is formatted as text before pasting. – Apostles 3/1, 2017 at 13:2

Which is exactly what OP doesn't want to do – Pelops 3/1, 2017 at 13:3

@Uvea Definelty works, you just need to format the col first – Choreodrama 3/1, 2017 at 13:3

I'm just curious if there's a way to do this...the "ctrl+v" way. If there's a script that I can put into the file that makes me able to use "ctrl+v" and still keep the correct format. – Uvea 3/1, 2017 at 13:4

@Pelops he doesnt want to manipulate the data. He wants to paste it as is, and it be exactly the same. – Choreodrama 3/1, 2017 at 13:4

@Uvea You could write a VBA macro that uses paste special, then create a custom keybind for it – Choreodrama 3/1, 2017 at 13:5

@Choreodrama Now you're talking. But how would I go around doing something like that? Only written one VBA-script before. – Uvea 3/1, 2017 at 13:6

If you want your column to be formatted as number you must use a custom format. In case of 14 digits number, you can format as "00000000000000", this way your number will be displayed in 14 digits. – Pasho 3/1, 2017 at 13:10

@PedroManuelVicenteMartins sadly I don't know if it's 8, 12, 13 or 14 numbers long. So I have to trust that they paste the correct length of the number with the correct amount of leading zeroes. – Uvea 3/1, 2017 at 13:13

@Uvea In this case you have two choices: 1) paste the numbers in a text file and then import it to Excel using VBA to see the length of each number and format accordingly. 2) Use the above solution using all column formatted as text before pasting anything. – Pasho 3/1, 2017 at 13:23

I just tested this, if the cell is already formatted as Text before pasting, then the Paste Special > Text will preserve the leading zeroes. As a keyboard shortcut, CTRL-ALT-V does Paste Special. – Rebarbative 15/12, 2020 at 15:51

@DanHenderson Thanks for the formatting tip. If formatting is kept as "Generic" no paste will ever work. When formatting is "Text", then Paste Special will work. – Watson 12/12, 2023 at 11:31

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

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

Choreodrama answered 3/1, 2017 at 13:10 Comment(5)

Sorry if I sound like a beginner now but...where exactly do I put this code? I would assume it's in the Alt+F11 menu somewhere. – Uvea 3/1, 2017 at 13:14

Took me a while to find the developer tab in my Swedish version...but the solution works! – Uvea 3/1, 2017 at 13:29

It appears that sometimes it does paste in the "next" (to the right) column for some reason. Any idea? – Uvea 16/1, 2017 at 14:2

@Uvea Cant explain that. Could have something to do with your chosen keybind as the code shouldn't do that at all. – Choreodrama 16/1, 2017 at 14:7

It just occurred to me that you could add another line in the macro to first set the cell's format to Text, before the existing line that does the pasting, couldn't you? – Rebarbative 23/1 at 19:35

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>

Determine answered 20/9, 2022 at 14:38 Comment(1)

This is great if you have the ability to coordinate with the owner of the system/tool that generates the source data from which you're copying! – Rebarbative 23/1 at 19:37

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

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

Elayne answered 26/3 at 5:0 Comment(1)

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Endotoxin 26/3 at 20:21

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags