LOSING leading zeroes

[tjak]

How can I tell XL (07) to DROP leading zeroes when I import an XL file? The incoming numbers I want scrubbed contain from 4 to 10 digits and may or may not have leading zeroes in them. The leading zeroes seem 'hard wired' onto the numbers as I can't get rid of them by re-formatting the cells or multiplying by 1, etc.
Or is there some other way to get VLOOKUP to compare a .csv file to the .xls file? I see the same numbers on both files, but VLOOKUP doesn't. I've never known leading zeroes to stop VLOOKUP.
Thanx.

 

[rafaelspeixoto]

The problem here is that those numbers are being imported as text, so you will have those leading zeroes and you will also have problems with vlookup (when a number is compared to a text it does not match). To make sure you have numbers stored as text you can use the formula =Type(A1). If the result is 1 you have a number, if it is 2 you have text.

To transform the numbers stored as text into actual numbers you can select the range, do a text to columns and select the output format as "number".

Hope this helps

Rafael

 

[tjak]

It came out "text", but I don't get "number" as an output option. The sender said the file is not protected and had no suggestions on how to deal with this. The target column in the .csv file converts to numbers as per usual - it's the .xls file that won't change the data type.
Maybe here's the game-changer: I just found out that the target data in the .xls file were originally hyperlinks (no, I don't know why). They re-sent the data after removing the hyperlinks. Could this be helpful?

 

[rafaelspeixoto]

Sorry, my bad. Select general as the output option of the text to columns and the numbers stored as text should be converted.
If this doesn't work I cans send you a VBA code to do it.

 

[tjak]

Already tried that with no luck. Let's try VB. Thanx for your help!

 

[rafaelspeixoto]

First of all, try entering a cell (press F2) and hit enter to see if it changes to a number. It should work. If it does, you can use the code below:

sub test
range("A1:A100").value = range("A1:A100").value
end sub

 

[tjak]

No go. The data is still text.

Thanx for trying, though.