Odd Number Format

marstonk

Active Member
Joined
Jan 30, 2003
Messages
273
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,909
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2003
Messages
273
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,909
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,909
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,446
Members
425,546
Latest member
DisMissive

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top