VBA keep leading zeros

[rurounisena]

Sub BO_ID_Prep()


Dim rng As Range


Dim i As String


For Each rng In Selection


i = i & rng & ";"


Next rng


ActiveCell.Offset(1, 1).Value = Trim(i)


ActiveCell.Offset(2, 1).Select


ActiveCell.FormulaR1C1 = "=LEFT(R[-1]C,LEN(R[-1]C)-1)"


Selection.Copy


ActiveCell.Offset(1, 0).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False


End Sub


The above script takes all selected cells and puts their data into one cell separated by a ;

[TABLE="width: 500"]
<tbody>[TR]
[TD]2345[/TD]
[TD]2345;3658;2499[/TD]
[/TR]
[TR]
[TD]36568[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2499[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



My issue is when the numbers have leading zeros they are dropped when being added.

[TABLE="width: 500"]
<tbody>[TR]
[TD]0234[/TD]
[TD]what it does 234;35;2345[/TD]
[/TR]
[TR]
[TD]0035[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2345[/TD]
[TD]what I want 0234;0035;2345[/TD]
[/TR]
</tbody>[/TABLE]

Can someone please help me with this?

 

[Fluff]

When I try that it looses the format.

 

[SpillerBD]

My experience with Outlook is that Outlook Exports to a CSV or PST file. I would assume you are exporting to a CSV file.
Opening a CSV file with Excel allows too many assumptions.
Instead, use the Import Text File, Data\Get External Data\From Text File, and during that process make sure to designate that field to be imported as Text.

 

[rurounisena]

Got it to work! Thank you both for your help!

 

[Rick Rothstein]

When I try that it looses the format.

I assumed the cells are formatted as text (in order to maintain the leading zeros)... if so, then the format is not lost.

 

[Fluff]

In that case I suspect that you missed post#4

custom format to show leading 0's

 

[Rick Rothstein]

In that case I suspect that you missed post#4

Yep, missed it. I saw Message#7 and figured they could not be custom formatted given the different overall size... I was wrong.

Okay, given that, here is a macro that, while not as streamlined as my original posted code, is still slightly more streamlined (one code line shorter) than your slightly streamlined macro...

Sub BO_ID_Prep()
  Dim Cell As Range, Combo As String
  For Each Cell In Selection
    Combo = Combo & ";" & Cell.Text
  Next
  Selection(1).Offset(, 1) = Mid(Combo, 2)
End Sub