VBA excel extra spaces in concatenation

Question

I have a VBA code in which I have to loop through a certain range and fill it using some concatenation but my VBA code is adding undesired blank spaces. For example, let's assume the following example:

code = "NV"
ac = "Curncy"

With wsNew

Set rng = .Range(Cells(2, 2), Cells(2, 2).End(xlDown))

For Each r In rng
    
    If r.Value = 17 Or r.Value = 18 Or r.Value = 19 Then
        
        r.Offset(, 1) = code + r.Offset(, -1).Value + Str(Right(r.Value, 1)) + " " + Str(r.Value) + _
        " " + ac
    
    Else
    
        r.Offset(, 1) = code + r.Offset(, -1).Value + Str(Right(r.Value, 1)) + _
        " " + ac
    
    End If

Next r

End With

For the first cell (r variable in the example), I have r.Value = 18 and r.offset(,-1).Value = U and I was expecting to obtain a value of NVU8 18 Curncy for my r.Offset(, 1). Instead, the code is adding spaces and I get NVU 8 18 Curncy with one space between U and 8 (to be removed) and 2 spaces between 8 and 18 instead of just one.

Use Cstr and not Str. ALso Use & for concatenation and not +. — freeflow, Nov 15 '20 at 18:51

BigBen · Accepted Answer · 2020-11-15T18:29:04.030

From the Str docs:

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.

Don't use Str, and use & to concatenate, instead of +. & forces string concatenation of two expressions.

r.Offset(, 1).Value = code & r.Offset(, -1).Value & Right(r.Value, 1) & " " & r.Value & _
        " " & ac

score 2 · Answer 2 · answered Nov 15 '20 at 18:37

BigBen gave the solution

here's a possible enhacement (see comments) of your code

With wsNew

    Set Rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown)) ' <-- use dots (".") before all range references !
    
    For Each r In Rng
        
        Select Case r.Value2 ' <-- "Select Case" syntax  can make it more readable and maintanable then a multiple If Then Else
            Case 17, 18, 19
            
                r.Offset(, 1).Value2 = code & r.Offset(, -1).Value2 & Right$(r.Value2, 1) & " " & r.Value2 & " " & ac
        
            Case Else
        
                r.Offset(, 1).Value2 = code & r.Offset(, -1).Value2 & Right$(r.Value2, 1) & " " & ac
        
        End If
    
    Next

End With

Use of Value2 property gets rid for possible unwanted pieces (see this answer)

Thanks for the dot advice... and I forgot about the Select Case statement in VBA, duly noted! — CTXR, Nov 15 '20 at 18:48

VBA excel extra spaces in concatenation

2 Answers2