Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numeric field from type 1 to type 2
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numeric field from type 1 to type 2
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... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "u473" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numeric field from type 1 to type 2
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to convert date type to text type | Excel Discussion (Misc queries) | |||
Excel data numeric type read problem | Excel Programming | |||
Excel data numeric type read problem | Excel Programming | |||
Converting text data type to numeric | Excel Programming |