Solved

leading apostrophe vlookup?

Posted on 2015-01-19
4
173 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now