Odd Number Format

[marstonk]

Hi

I am doing a VLookup on a list of numbers however 2 numbers in my list are not returning a value when they should be. I have tried all the formatting options I can think of. All the nos are the same length and the 2 not returning values are slightly different in that they appear slightly to the left of the cell unlike all the other numbers - Anyone have any ideas?

Kind Regards
Kitty g

 

[texasalynn]

Do a test to see if all the data is the same format. On the sheet that you are going to in a blank cell type in =istext(a1) (change range as needed) and copy this down. You will probably on need to do this on the range where the data you can't get the results from and then maybe on a couple of rows that do work. You will probably find the data is not the same type.

HTH
texasalynn

 

[Joe4]

If the numbers appear to be left justified, and you did not do any centering/formatting, then the values are probably entered as Text instead of Numeric, and that is probably why the VLOOKUP isn't working on those values.

Change them to numbers and they should work OK.

 

[marstonk]

Yeah they return false - how do I change there format I tried to format cell to text but still it doesnt recognise them.

kitty g

 

[Joe4]

If you highlight the column and do a "Text to Columns" (under the Data menu) on that column, you can change the whole column to Text or Numeric, depending on what you want.

 

[texasalynn]

I have found that after you change the cell's format to text you then need to "select" it - press F2 and then press enter.

Then the cell will be reconized as text.

HTH
texasalynn

 

[marstonk]

Cheers guys

 

[Joe4]

I have found that after you change the cell's format to text you then need to "select" it - press F2 and then press enter.

By selecting it, pressing F2, and pressing enter, you are re-entering the value. That is what the Text to Columns is actually doing, but doing it on the whole column at once instead of each cell individually.

The Text to Columns method is a great tool for data cleanup. I use it very often, especially when you need to re-format data.