Ask Your Question

vlookup not working unless I press delete after search criterion [closed]

asked 2013-07-10 05:17:44 +0100

Dan52 gravatar image

I have 2 sheets that I am using vlookup to find and fill in data from one to the other. I am using a 6 digit number for the search criterion which is listed once in both sheets. I get #N/A unless I go to the cell referenced for the search criterion in the first sheet and press delete after the number. It doesn't seem to have anything there and I can't space over but this fixes the problem. Can anyone help with a better solution than what I am currently doing?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-03 21:14:59.734820

1 Answer

Sort by » oldest newest most voted

answered 2013-07-10 07:58:46 +0100

razon_22 gravatar image

There might be a non-printable character after each number. You can use the LEFT function to isolate the 6 digits in each cell on your first sheet. In a blank column on sheet 1, say, column F, enter the formula:

=LEFT(A2, 6)

Go to the handle of the cell with the formula and drag the formula down to the last row of data. Select the cells in column F, copy, paste special - values over the original data.

edit flag offensive delete link more



Big thanks, worked and saved me a bunch of time.

Dan52 gravatar imageDan52 ( 2013-07-10 08:35:49 +0100 )edit

Question Tools

1 follower


Asked: 2013-07-10 05:17:44 +0100

Seen: 588 times

Last updated: Jul 10 '13