Convert numeric field from type 1 to type 2

[u473]

My Lookup does not work because the LookUp field is sometimes of type
1 and sometimes type 2
I tried to fix that in the initial import step by converting the
culprit field to a string as follows :
..
Sub Convert()
Dim LastRow As Integer
LastRow = Range("E65335").End(xlUp).Row
Range("E1:E" & LastRow).NumberFormat = "@"
End Sub
..
The result leads me to believe that the conversion took place because
the data is now is Left-Aligned.
However the data type in that field is still unchanged.
Data in my source field in sometimes Numeric like 51693, sometimes
AlphaNumeric like TN101,
I need to have all that field converted to String or Text.
..
Thank you for your help,
..
J.P.

 

[Sheeloo]

You do not need to convert everything to text to make VLOOKUP work...

Instead of
=VLOOKUP(A1,...)
Use
=VLOOKUP(TEXT(A1,"#"),...
if your lookup range has text and A1 has a number...

 

[u473]

I do appreciate this VLOOKUP tip as I will have the use for it.
However I would like to fix the data type in my initial Import step
with VBA in order to have all data type for that field as type 2.
But I read in another post from Tom Ogilvy : "In general, you can't
force a change in type in VBA the way you can in Excel..."
I could accept that if the conversion was applied within the Cell
within the same Sheet,
but I am reading data in workbook A, converting and writing it in
workbook B. What is the work around solution ?
Thank you for your help,
J.P.