Solved

leading apostrophe vlookup?

Posted on 2015-01-19
4
264 Views
Last Modified: 2015-01-19
The attached sheet is a result of a macro.

Column B contains a number of product codes. All of which have a  leading ' to prevent the loss of leading 0's

The problem is I need to v lookup these codes against another sheet with the same codes but on the second sheet the codes don't have leading apostrophes

Any ideas
Stock-report-Utility1.xlsm
0
Comment
Question by:robmarr700
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:James Elliott
James Elliott earned 250 total points
ID: 40557348
I think your problem is more-caused by the trailing spaces that each of your codes on Sheet 1 has.

You'll need to TRIM these in place, or in a seperate column before looking up against a list of codes without trailing spaces.

The apostrophe shouldn't in itself be a barrier to vlookups.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40557354
However, they are formatted as text, which is the same thing.

The leading ' is not a real character - it is just an indication to Excel that it is formatted as text.

To prove it, go to Sheet1!A5 and enter

=LEFT(B5,1)

B5 contains: '10301                 . If the leading ' was a real character, then =LEFT(B5,1) would equal ', but it equals 1.

So ignore the leading 's; they are not going to cause you a problem.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40557359
What is going to cause you a problem is the leading spaces. So create a new column before column Sheet1!A, and have in there:

=trim(c2)

You can then use this new column A as the basis of your lookup.

If that is not possible, then column Sheet1!B has 23 characters. So column Sheet2!C:C could be:

=VLOOKUP(LEFT(B1 & REPT(" ",23),23),Sheet1!B:C,2,FALSE)

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40557398
Which sheet are you trying to put the VLOOKUP on and which sheet are you looking up from?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question