Str adds extra space [duplicate]

Question

Dim i As Integer

i = Int((8 - 2 + 1) * Rnd + 2)

Dim rg As String
Dim con As String

con = Str(i)

rg = "B" & con
MsgBox (rg)

This returns "B 4" not "B4 anyone know the issue

Answer 1

Use Cstr(i) rather than Str(i). Cstr does not add a space.

Answer 2

From the Help page for Str()

When numbers are converted to strings, a leading space is always reserved for the sign of number. If number is positive, the returned string contains a leading space and the plus sign is implied.

Answer 3

Str() leaves space for the sign.

As Excel has implicit conversion, you can use rg = "B" & i and get the range you want

Answer 4

Use format() function ...

con = format(i)

rg = "B" & con
MsgBox (rg)

Answer 5

Use the Trim function to remove leading space as under:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer

i = Int((8 - 2 + 1) * Rnd + 2)

Dim rg As String
Dim con As String

con = Str(i)

rg = "B" & Trim(con)
MsgBox (rg)
End Sub