Excel VBA How to convert cell format text to number
<p>What my code do is that it will automatically get all the Excel files list in the folder and compare the Serial number on master list column then if the value is same it will copy the serial number and paste it on the C column. If both of the column formats are the same then it will work perfectly. But if the column is formatted in text, the comparison seems to fail. I've been searching quite some time to convert these cells to number format or general format so that the comparison can be successfully done without the concern on the format.</p>

<p><a href="https://i.stack.imgur.com/u763G.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/u763G.png" alt="enter image description here"></a></p>

<p><a href="https://i.stack.imgur.com/jzU66.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jzU66.png" alt="enter image description here"></a></p>

<pre><code>Sub Compare()

Dim Dic As Object
Dim fso As Object
Dim fldStart As Object
Dim fl As Object
Dim Mask As String, i As Long
Dim Wbk As Worksheet, w1 As Worksheet

Set fso = CreateObject("scripting.FileSystemObject")
Set fld = fso.GetFolder("C:\Users\kelvinwong\Desktop\Survey Testing")
Set w1 = Workbooks("Book1.xlsm").Sheets("Sheet1")

Set Dic = CreateObject("Scripting.Dictionary")

Mask = "*.xlsx"

For Each fl In fld.Files
    If fl.Name Like Mask Then
        Set Wbk = Workbooks.Open(fld &amp; "\" &amp; fl.Name).Sheets("Sheet1")
        i = Wbk.Cells.SpecialCells(xlCellTypeLastCell).Row
        For Each oCell In Wbk.Range("A2:A" &amp; i)
            If Not Dic.exists(oCell.Value) Then
                Dic.Add oCell.Value, oCell.Offset(, 0).Value
            End If
        Next oCell

    End If
Next fl

i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row

For Each oCell In w1.Range("A2:A" &amp; i)
    For Each key In Dic
        If oCell.Value = key Then
            oCell.Offset(, 2).Value = Dic(key)
        End If
    Next
Next

End Sub
</code></pre>