MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Reduciing number lengths


Posted by Terry on November 23, 2001 6:28 AM

I need to regularly reduce columns of 6 digit numbers (integers) to just the first 5 digits i.e. 123456 becomes 12345. (The resultant numbers are then to be used used in a VLOOKUP function) - or could I modify VLOOKUP to ignore the last digit?

I suspect there's a extremely easy way of doing this but I can't see it!

Many thanks


Posted by Ian Mac on November 23, 2001 6:56 AM

=VLOOKUP(LEFT(A1,5),Range,Column,0) (you can change the 0 to the number you want) (NT)

Posted by IML on November 23, 2001 7:36 AM

If that "incorrectly" produces the dreaded n/a ....

try modifying it slightly to
=VLOOKUP(LEFT(A1,5)+0,Range,Column,0)