Remove a leading space from a range
<p>I have a column range of about 500 rows. Most of those cells are stored as text. I populate a listbox on a userform with the values from that range. When a user selects one of those values from the listbox an event will find the value on the same row from another column using Index and Match and display it in a label on the userform. I get an error when selecting one of the few cells in the listbox that are not stored as text in the range because there is a leading space. I am assuming that the populated listbox automatically removes leading spaces from any cells in the range. Therefore, when it tries to find value 12345 from the listbox, for example, in the range it can't find it because the range contains (space)12345. I have tried:</p>

<pre><code>Public Sub UserForm_Initialize()

Dim arr() As Variant
Dim rNum As Range

Const sNum As String = "Number"

Me.EnableEvents = False

wsName = "Report"
Set curWb = ActiveWorkbook
Set pReport = curWb.Worksheets(wsName)
Set pTable = pReport.ListObjects("tableName")

With pReport
    If .AutoFilterMode = True Then .ShowAllData
    .Cells.Rows.Hidden = False
    .Cells.Columns.Hidden = False
End With

Set wf = Application.WorksheetFunction

With pTable
    Set rNum = .ListColumns(.ListColumns(sNum).Range.column).DataBodyRange
End With

-- HERE is where I tried all my implementations without success

arr = wf.Transpose(pReport.Range(rNum.address).Value)

Call BubbleSort(arr)

frmIssues.lstIssues1.List = arr

lstIssues1.ListStyle = 1
lstIssues2.ListStyle = 1
lstIssues1.MultiSelect = 2
lstIssues2.MultiSelect = 2
txtFocus.SetFocus

Me.EnableEvents = True

End Sub
</code></pre>

<hr>

<pre><code>Private Sub lstIssues1_Change()

Dim rNum As Range
Dim rTitle As Range
Dim strResult As String
Dim intIndex As Integer
Dim intCount As Integer

Const sNum As String = "Number"
Const sTitle As String = "Title"

If EnableEvents = False Then Exit Sub

With lstIssues1
    For intIndex = 0 To .ListCount - 1
        If .Selected(intIndex) Then intCount = intCount + 1
    Next
End With

If intCount = 1 Then
    Set wf = Application.WorksheetFunction
    wsName = "Report"
    Set curWb = ActiveWorkbook
    Set pReport = curWb.Worksheets(wsName)
    Set pTable = pReport.ListObjects("tableName")

    With pTable
        Set rNum = .ListColumns(.ListColumns(sNum).Range.column).DataBodyRange
        Set rTitle = .ListColumns(.ListColumns(sTitle).Range.column).DataBodyRange
    End With

    With pReport
        strResult = wf.Index(.Range(rTitle.address), wf.Match(lstIssues1.List(lstIssues1.ListIndex), .Range(rNum.address), 0))
    End With

    lblDescription.Caption = wf.Trim(strResult)
    txtFocus.SetFocus
Else
    lblDescription.Caption = ""
    txtFocus.SetFocus
    Exit Sub
End If

Me.EnableEvents = False

For i = 0 To lstIssues2.ListCount - 1
    If lstIssues2.Selected(i) = True Then lstIssues2.Selected(i) = False
Next

Me.EnableEvents = True

End Sub
</code></pre>

<p>and numerous variations of it (Clean, CStr, .Text, etc.) and nothing works. Truly, I have no clue how to fix this and any help whatsoever is much appreciated. Thank you!</p>

<p><strong>Clarification</strong></p>

<p>1) This Excel file is generated from the Web.</p>

<p>2) A Macro turns the Worksheet into a table</p>

<p>3) <code>Left(Range("D362"),1)</code> returns <code>1</code> (The number, say, is 12345)</p>

<p>4) Before the error occurs <code>Range("D362")</code> returns <code>(space)12345</code></p>

<p>5) After the error occurs <code>Range("D362")</code> returns <code>(space)12345</code></p>