Concatenation and keeping leading zeros

[alexransome]

Hello,

This is part of the code I have in my sheet which takes the value from each cell and joins them in a third cell.

' Concatenate 1st Part
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(i, "B").Value = Cells(i, "C").Value & " -- " & Cells(i, "E").Value
Next I

The problem is the cells in 1 and 2 have been formatted with a leading zero, so a "1" becomes "01", but when the concatenation takes place the result generated is "1 -- 1", when it should be "01 -- 01".

For bonus points, the "--" is supposed to be a "/", but when that is added to a cell it is formatted to a date type. I know that if I add a " ' " to the cell then it is accepted, but not sure how to get that to happen within a VBA script.

Many thanks for any assistance.

 

[sastoka]

Hi there,

This will format your destination cell as TEXT. il will kep leading zero and /

' Concatenate 1st Part
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B2").Select
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
       Cells(i, "B").NumberFormat = "@"
       Cells(i, "B").Value = Cells(i, "C").Value & " -- " & Cells(i, "E").Value
   Next I

Regards,

 

[FormR]

Hi, another one for you try:

Cells(I, "B").Value = "'" & Cells(I, "C").Text & "/" & Cells(I, "E").Text