Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 10 Jul 2013 08:35:49 +0200vlookup not working unless I press delete after search criterionhttps://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/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?Wed, 10 Jul 2013 05:17:44 +0200https://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/Answer by razon_22 for <p>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?</p>
https://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/?answer=20075#post-id-20075There 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.Wed, 10 Jul 2013 07:58:46 +0200https://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/?answer=20075#post-id-20075Comment by Dan52 for <p>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:</p>
<blockquote>
<p>=LEFT(A2, 6)</p>
</blockquote>
<p>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.</p>
https://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/?comment=20078#post-id-20078Big thanks, worked and saved me a bunch of time.Wed, 10 Jul 2013 08:35:49 +0200https://ask.libreoffice.org/en/question/20074/vlookup-not-working-unless-i-press-delete-after-search-criterion/?comment=20078#post-id-20078