Solved

leading apostrophe vlookup?

Posted on 2015-01-19
4
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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