Pasting from column B into column D

Question

I have to copy/paste item codes from column B into column D.

Most of the cells in column B are like this:

XX787 DO BOLOGNESE 2X2.28KG FR

The item code is the first 5 characters in this case.

A few cells in column B are like this:

01333379 Rice Long Grain PB Milled 10 BB

In this case, the item code is 8 characters and consists of numbers + the first character is a zero.

The issues:

• The zero at the begin is not displayed when I copy/paste from column B into column D. I tried pasting all the cell values as text so that the zero would be displayed, but it didn't help.
  I used this code to format as text: ActiveSheet.Range("B:D").NumberFormat = "@"
• The first characters are put in column B, but when it more characters, the rest of the characters does not get put in.

Answer 1

Just change the target cells format to text before running the macro. If you do it after running the macro the zero won't be there.

Answer 2

This code is working fine for me and is not deleting leading zeros:

Dim i As Integer

'Set the numberformat of column D to text
Range("D1").EntireColumn.NumberFormat = "@"

'Loop through the used rows
For i = 1 To UsedRange.SpecialCells(xlCellTypeLastCell).Row Step 1

    'assign the item code to column D
    Cells(i, "D").Value = Split(Cells(i, "B").Value, " ")(0)
Next i

To Explain the Split() function: Split() splits the specified String (Cells(i, "B").Value in this case) into an array, you can specify the delimiter (which in this case is a space (" ")).

Since the returnvalue is an array, we can tell VBA to only return the first value, which should be the item code. This is done by adding (0) at the end of the line.