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]

Are the values text or are they numbers with a custom format to show leading 0s

 

[Rick Rothstein]

Are the values in the cells you select constants or do the cells have formulas in them?

Are the cells you select formatted as Text or, if not, do they have an apostrophe in front of them when viewed in the Formula Bar?

 

[rurounisena]

Are the values text or are they numbers with a custom format to show leading 0s

custom format to show leading 0's

 

[Fluff]

In that case try

i = i & Format(rng, "0000") & ";"

Change the number of 0s to match your format

 

[rurounisena]

Are the values in the cells you select constants or do the cells have formulas in them?

Are the cells you select formatted as Text or, if not, do they have an apostrophe in front of them when viewed in the Formula Bar?

The numbers are state id's exported from Outlook. For example, 0747717 is the correct id. When exported to excel, its shown as 747717. I then do text to columns and format with the correct amount of numbers (in this case "0000000" but it varies by state. When I run the macro it reverts back to how it was before I formatted it.

 

[rurounisena]

Thank you! That works but my issue is that the number of 0's can change. For this file it needs to be "0000000" but for another it could be "0000"

 

[Fluff]

Ok, try

i = i & rng.Text & ";"

 

[Fluff]

A slightly streamlined version

Sub BO_ID_Prep()
   Dim i as String
   Dim j As Long
   
   For j = 1 To Selection.Rows.Count - 1
      i = i & Selection(j).Text & ";"
   Next j
   i = i & Selection(j).Text
   ActiveCell.Offset(, 1).Value = i
End Sub

 

[Rick Rothstein]

A slightly streamlined version

Sub BO_ID_Prep()
   Dim i as String
   Dim j As Long
   
   For j = 1 To Selection.Rows.Count - 1
      i = i & Selection(j).Text & ";"
   Next j
   i = i & Selection(j).Text
   ActiveCell.Offset(, 1).Value = i
End Sub

A little bit more streamlined...

Sub BO_ID_Prep()
  Selection(1).Offset(, 1) = Join(Application.Transpose(Selection), ";")
End Sub