Number is in odd format

[Gaveon]

Hello,

I am getting a generated report from a customers website that i wish to extract data from
The column i wish to work with is an ID eg. 10001702 that i wish to use in a VLOOKUP.

The problem is the number is in an odd format, text or something so when i try to VLOOKUP it cannot recognize the number
Left justified like text instead of right justified like a number
clicking format cells and changing to number does not solve the issue

if i click on the cell containing the number, then click on the formula bar, then press enter, it will become a number. While this works its still a tedious method.

Is there a way i can find out how the number is formatted so i can set the vlookup to search for that?
or
is there a way i can add another column to in which it converts the number into a number format.
or any other solutions?

Hope people can follow that.

Thanks for any help

[noboffinme]

Hi Gaveon,

Try adding a new column that multiplies your value by 1.

So the Formula for a value in 'A2' would be;

=A2*1

This will convert it into a number.

Do this to both & the VLOOKUP should work.

Hth

[Gaveon]

That did the trick.

Thankyou very much

[FDibbins]

Hi and welcome to the forum

Another way to do this without a helper, is to highlight the entire range of "numbers", scroll back to the top, and if you look at the 1st cell that you highlighted, you will see a small yellow diamond - click on it and select "convert to text"

If this is going to be a frequent problem, convert the value you are trying to find, to text...=vlookup(TEXT(A2,"#"),range,column,0)