Solved

Posted on 2015-01-19
283 Views
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
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
• 2

LVL 12

Assisted Solution

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

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

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)
``````
0

LVL 85

Expert Comment

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

## Featured Post

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.
###### Suggested Courses
Course of the Month7 days, 9 hours left to enroll