Auto Shorten a Data String

I have 2 worksheets that I wish to perform Vlookup references from...
the lookup data comprises of a 6 digit numeric value.
My problem is that on one of the sheets the data string has been augmented
by a 7th character and I am looking for a way to delete the last character in
this string and allow me to do the lookup between 6 digit values ??

ie. Worksheet 1 ... data 123456 .... Worksheet2 1234567 ...
I wish to drop the 7 in this case

Is there an automated way to do this as I have 10,000 entries ??

Many Thanks

Alan

Assuming that 1234567 is in B1,B2,B3, etc. use the formula =left(B1,6) in C1 and copy down as far as you need to. Then copy (c1 to c10000, say) and paste special values in column B.

Is this what you were after?

Cheers
free

that would work, or if you're looking up on a 7-digit numbers to a list of 6-digits ones, use

=VLOOKUP(LEFT(A1,6),Array,...)

Please Ignore ... Found the LEFT Function and it does the trick

Many thanks anyways

Alan

"Alan" wrote:

> I have 2 worksheets that I wish to perform Vlookup references from...
> the lookup data comprises of a 6 digit numeric value.
> My problem is that on one of the sheets the data string has been augmented
> by a 7th character and I am looking for a way to delete the last character in
> this string and allow me to do the lookup between 6 digit values ??
>
> ie. Worksheet 1 ... data 123456 .... Worksheet2 1234567 ...
> I wish to drop the 7 in this case
>
> Is there an automated way to do this as I have 10,000 entries ??
>
> Many Thanks
>
> Alan