Solved

leading apostrophe vlookup?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

867 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

26 Experts available now in Live!

Get 1:1 Help Now